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

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;