CODEWITHSUNDEEP
macrossas
user3658367
user3658367

Reputation: 641

Proc SQL macro variable not resolved

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 ℴ

LOG: 

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

Upvotes: 0

Views: 823

Answers (2)

Tom
Tom

Reputation: 51621

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 ℴ

Upvotes: 4

DomPazz
DomPazz

Reputation: 12465

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.

Upvotes: 0

Related Questions