SAS Proc SQL INTO - Cannot write a macroed variable into macro?

Question

data COLUMN_LIST_DATA;
length COLUMN_LIST CATEGORY $8.; 
input COLUMN_LIST CATEGORY;
datalines;
COL1 MY_COLS
COL2 OTHER
COL3 MY_COLS
COL4 OTHER
COL5 OTHER
;
run;

%macro TBL_Keep(var); %macro _; %mend _;

        PROC SQL NOPRINT;
            *Create SELECT statement for columns we want;
            SELECT COLUMN_LIST INTO: &var. SEPARATED BY ' '
            FROM COLUMN_LIST_DATA
            WHERE CATEGORY = "&var."
            ;
        QUIT;
%mend;

%TBL_Keep(MY_COLS);
%put &MY_COLS.;

Not sure why the above code doesn't work.... Does INTO not accept macroed variables as input? If I write the same code, manually substituting the macroed "&var." with "MY_COLS" it works fine.

Answer 1

First problem is that if the macro variable named MY_COLS does not already exist before the call to %TBL_KEEP() then the macro variable will be LOCAL and disappear when the macro execution ends.

You could just set it to some default value before calling the macro to insure it exists.

%let my_cols=BEFORE THE CALL;
%TBL_Keep(MY_COLS);
%put &=my_cols;

Or make the macro smart enough to force the macro variable to exist by issuing a %GLOBAL statement. While changing the macro definition you should also fix the second issue: If no observations match the WHERE condition on the query then the macro variable's value will not be changed.

%macro TBL_Keep(var); 
%if not %symexist(&var) %then %global &var;
%let &var= ;
PROC SQL NOPRINT;
SELECT COLUMN_LIST
  INTO :&var. SEPARATED BY ' '
  FROM COLUMN_LIST_DATA
  WHERE CATEGORY = "&var."
;
QUIT;
%mend TBL_Keep;