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, Commented Sep 12, 2017 at 15:59
@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
– user3658367, Commented Sep 12, 2017 at 16:05
Why are the WHERE clauses different? The results won't like up if they're not the same. — Reeza
– Reeza, Commented Sep 12, 2017 at 16:11

Tom · Accepted Answer · 2017-09-12 16:37:37Z

4

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;

edited Sep 12, 2017 at 16:37

answered Sep 12, 2017 at 16:12

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DomPazz · Accepted Answer · 2017-09-12 15:50:23Z

0

The names are being created as LOCAL values inside the macro and are not available outside it. Try adding

%global NAMES ORDER;

to the macro before the SELECT statements.

answered Sep 12, 2017 at 15:50

DomPazz

12.4k19 silver badges23 bronze badges

2 Comments

user3658367 Over a year ago

Now for the statement %put &order it is blank.

DomPazz Over a year ago

See Tom's answer. The missing %GLOBAL was the reason for the error.

Collectives™ on Stack Overflow

Proc SQL macro variable not resolved

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related