topic Re: Proc SQL select to create macro - Not running as nested macro in SAS Programming

Proc SQL select to create macro - Not running as nested macro

LEINAARE — Thu, 27 Dec 2018 16:14:41 GMT

Hello,

I am using the SQL procedure to create macros as written below. When I write the code below, macros are created as desired and work as global macros outside the SQL procedure.

proc sql noprint;
    select quote(strip(diagnosis)) into :PregnanacyDiag separated by ',' from PregnancyDiag;
quit;

%put &PregnanacyDiag; /*appropriate code is written to the log*/

I am using the above procedure many times to create macro variables containing quoted strings of code from numerous datasets which contain procedural, diagnostic, and drug codes. Accordingly, it makes sense to convert this code into a macro. I attempted to do so as show below.

%macro makemac(var,macname,dataset); 
    select quote(strip(&var)) into :&macname separated by ',' from &dataset;
%mend;

proc sql noprint;
     /*Diaphragm Lookup Codes*/
     %makemac(procedure,ProcMAC_Diaphragm,DiaphragmProc);
     %makemac(ndc,NdcMAC_Diaphragm,DiaphramNDC);
     /*Implant Lookup Codes*/
     %makemac(diagnosis,DiagMAC_Implant,ImplantDiag);
     %makemac(procedure,ProcMAC_Implant, ImplantProc);
     %makemac(ndc,NdcMAC_Implant,ImplantNDC);
quit;

%put &ProcMAC_Diaphragm;

I expected that the SQL procedure would run as normal with the '%makemac' macro inserting appropriate syntax for select statements for each line of code. When I run the program, the log appears normal. I used the 'symbolgen' option, and it shows the appropriate values replaced for each line of macro code. But when I try to use the '%put &(macro name)' statement, it does not write the string of quoted code to the log I expected, and I receive instead the warning below.

"WARNING: Apparent symbolic reference PROCMAC_DIAPHRAGM not resolved."

If anyone could offer suggestions to fix this, I would greatly appreciate it. I am using this code many times. When I hard code it, it works as desired. But streamlining the code as a macro makes more sense to me. Also, I'd just like to understand the error.

Thanks,

Ted

Re: Proc SQL select to create macro - Not running as nested macro

novinosrin — Thu, 27 Dec 2018 17:16:12 GMT

Unfortunately PROCMAC_DIAPHRAGM is a local macro variable and your %put happens to execute outside the macro definition

Macro parameters are local

Re: Proc SQL select to create macro - Not running as nested macro

LEINAARE — Thu, 27 Dec 2018 17:18:32 GMT

Hi @novinosrin,

Thank you for clarifying this. I guess I will have to figure out a different strategy.

Re: Proc SQL select to create macro - Not running as nested macro

novinosrin — Thu, 27 Dec 2018 17:23:24 GMT

My bad.

ProcMAC_Diaphragm

is a macro variable value in the macro call and not a macro parameter. I think I have to go and get my eyes checked.

Your macname is a local macro variable that is local to the macro definition makemac. Sorry I am just waking up. Holiday laziness. Ignore my previous post

Re: Proc SQL select to create macro - Not running as nested macro

Astounding — Thu, 27 Dec 2018 17:24:18 GMT

To make your variables global instead of local, change the macro definition. Currently, you have:

%macro makemac(var,macname,dataset); 
    select quote(strip(&var)) into :&macname separated by ',' from &dataset;
%mend;

Instead, use:

%macro makemac(var,macname,dataset); 
    %global &macname;
    select quote(strip(&var)) into :&macname separated by ',' from &dataset;
%mend;

The problem isn't that MACNAME is a parameter. &MACNAME is not a parameter. But the problem is that SQL creates its macro variables in the local symbol table. So define them as global ahead of time, as shown above.

Re: Proc SQL select to create macro - Not running as nested macro

LEINAARE — Thu, 27 Dec 2018 17:31:05 GMT

Hi @Astounding,

Thank you for your suggestion. That was a quick and easy fix. I had played with %global previously, but without success.

Ted

Re: Proc SQL select to create macro - Not running as nested macro

LEINAARE — Thu, 27 Dec 2018 17:33:51 GMT

Hi @novinosrin,

Thank you for clarifying the definitions. I am still learning macro language and proc sql.