Proc SQL macro variable not resolved

Question

I wrote a proc SQl normally first without any macro variables and it works, now I would like to convert it to macro and it doesnt work, can you see where the problem is ?

%macro macrova(LIB=, DATA=);

proc sql noprint;

/*creating an in memory variable with all char variables from the dataset*/

        select name into :names separated by ' '
        from dictionary.columns where libname="&lib" and memname="&data" and type='char'; 

/*to make sure we have the same order of the variables an in-memory variable ORDER is created*/

        SELECT NAME INTO: ORDER SEPARATED BY ' '
        from sashelp.vcolumn where LIBNAME= "&lib" and memname="&datA" ; 

quit;

%MEND;

OPTIONS MLOGIC SYMBOLGEN;
%macrova(LIB=SASHELP,DATA=CLASS)


%PUT &NAMES;
%PUT &ORDER;

LOG:

55   %PUT &NAMES;
WARNING: Apparent symbolic reference NAMES not resolved.
&NAMES
56   %PUT &ORDER;
WARNING: Apparent symbolic reference ORDER not resolved.
&ORDER

While it's perfectly legal to do so, it's a bit confusing to see dictionary.columns in one query and sashelp.vcolumn in the second... — Joe
@Joe ok, it happens when I use both sashelp.vcolumn and as suggested by DomPazz even after writing global statement, now it gives no names for the macro variable. — user3658367
Why are the WHERE clauses different? The results won't like up if they're not the same. — Reeza

Tom Tom · Accepted Answer · 2017-09-12T16:12:57

You need to either define the macro variables before calling the macro or add %GLOBAL statement to the macro. Otherwise the macro variabless will be created as local and disappear when the macro exits.

Note that there is no variable named ORDER in dictionary.columns, I assume you meant to use the VARNUM variable.

Also there is no need to run two queries to generate two macro variables.

%macro macrova(LIB=, DATA=);
%global names order ;
proc sql noprint;
select name
     , varnum
  into :names separated by ' '
     , :order separated by ' '
from dictionary.columns 
where libname=%upcase("&lib")
  and memname=%upcase("&data")
  and type='char'
order by 2
; 
quit;
%mend macrova;

%macrova(LIB=SASHELP,DATA=CLASS)
%put &NAMES;
%put &ORDER;

Proc SQL macro variable not resolved

2 Answers