The compiler internals of pretty much any programming language do similar tree to tree transformations: just not ones that the program itself can specify as part of its code. We don't call C self-modifying because a for(;;) loop structure was changed by the compiler into if/goto with generated labels, and then changed again into assembly code.
A macro that mutates the input source code on which it operates wouldn't pass a code review in any competent Lisp shop.
It can happen. For instance (setf (car '(1 2)) 2) is self-modifying code; it tries to replace the 1 with a 2, and that 1 is embedded in the program code itself, in a literal list. This is undefined behavior according to ANSI CL, very similarly to how "abc"[1]++ (modifying a string literal) is undefined behavior in C. Note that this code isn't implementing a macro.
I've never heard of compiled Lisp object code being mutated.
Lisp programs as such can be self-modifying; a common example of that is updating while running: loading new versions of existing modules, replacing old functions with new ones. That doesn't involve modifying code.
Doesn't mean I suggest doing this, but please do not sell unhygenic CL macros short. :) In fact, in CL one has to go to some lengths to be careful to make macro outputs "safe."
If we run code in a Lisp interpreter, then one can write self-modifying code. It's possible to get visible effects. It's also possible to use it during debugging code.