PROC SQL within SAS Macro to list all variables of a data set - SELECT Statement causing error

Question

I was trying to create a macro to output a list of all variables of a specific data set. In my macro, I am using PROC SQL. The code runs OK outside %macro, but error message saying the SELECT statement is not valid when it is being used within %MACRO

here is an example:

  proc sql noprint;
     select name into :vlist separated by ' '
     from dictionary.columns
     where memname = upcase("&dsn");
  quit;
  %put &vlist;

the above works perfectly;

but

%macro getvars(dsn);
%local vlist;
proc sql noprint;
    select name into :vlist separated by ' '
    from dictionary.columns
    where memname = upcase("&dsn");
quit;
&vlist;
%mend;

the above doesn't work when I tried to do:

%let var_list = %getvars(dataset);

it returns:

ERROR 180-322: Statement is not valid or it is used out of proper order.

underlining the SELECT statement within the PROC SQL

Answer 1

Seems like the only viable option for my use case is from the following SAS paper, under the section of "USING A MACRO LOOP"

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/028-30.pdf

To clarify, my use case need a direct output of the list itself, not a macro variable.

e.g.

data OUT (keep= %getvars(DATA_A) VAR_B1);
merge DATA_A (in=a)
      DATA_B (in=b)
      ;
run;

The PROC SQL won't work for me. So I think I need to move over to SAS I/O Functions in Macro Loop.

Below is from the SAS Paper:

%Macro GetVars(Dset) ;
  %Local VarList ;
  /* open dataset */
  %Let FID = %SysFunc(Open(&Dset)) ; 
  /* If accessable, process contents of dataset */
  %If &FID %Then %Do ;
  %Do I=1 %To %SysFunc(ATTRN(&FID,NVARS)) ;
  %Let VarList= &VarList %SysFunc(VarName(&FID,&I));
  %End ;
  /* close dataset when complete */
  %Let FID = %SysFunc(Close(&FID)) ;
  %End ;
  &VarList 
%Mend ;

Answer 2

SAS macros are not like functions in most programming languages: they don't return values, they are actually replaced by the content of the macro.

The solution is to make your macro variable global, outside the macro. Then you don't need to assign it to a new macro variable with %let.

%global vlist;
%macro getvars(dsn);
    proc sql noprint;
        select name into :vlist separated by ' '
        from dictionary.columns
        where memname = upcase("&dsn");
    quit;
%mend;
%getvars(work.class)
%put &=vlist;

[EDIT]
and then just use the list in your keep statement

data OUT (keep= &vlist. VAR_B1);
    merge DATA_A (in=a) DATA_B (in=b) ;
run;

Answer 3

%let var_list = %getvars(dataset);

will resolve to:

%let var_list = proc sql noprint;
    select name into :vlist separated by ' '
    from dictionary.columns
    where memname = upcase("dataset");
quit;

So it will store proc sql noprint in var_list, and then fail because you use proc sql outside of proc sql.

Answer 4

A macro using %SYSFUNC(DOSUBL( can run any amount of SAS code (in a separate stream) when invoked at source code parse-time.

Example:

data have_A;
  do index = 1 to 10;
    x = index ** 2; y = x-1; z = x+1; p = x/2; q = sqrt(x); output;
  end;
run;

data have_B(keep=B1);
  do index = 1 to 10;
    B1 + index; output;
  end;
run;

%macro getvars(data);
  %local rc lib mem names;
  %let rc = %sysfunc(DOSUBL(%nrstr(
    %let syslast = &data;
    %let lib = %scan (&SYSLAST,1,.);
    %let mem = %scan (&SYSLAST,2,.);
    proc sql noprint;
      select name into :names separated by ' ' from
      dictionary.columns where
        libname = "&lib." and
        memname = "&mem."
      ;
    quit;
  )));
  /* Emit variable name list */
  &names.
%mend;

data OUT (keep=%getvars(HAVE_A) B1);

merge HAVE_A (in=a)  /* 1:1 merge (no BY) */
      HAVE_B (in=b)
      ;
run;