A MACRO is just source code, like a VIEW. The definition is stored in the Data Dictionary (= dbc tables), thus doesn't need any perm space in the target database.
But the source code of an SP is rewritten as a kind of Embedded SQL program in C and then compiled to a Shared Object (Unix) or DLL (Windows). This SO/DLL is stored (internally probably as a kind of table) within the target database and materialized in the node's filesystem whenever CALLed. It's always stored on two AMP, original and fallback version.