Using SQL to pass lists of values into macro variables in SAS

Question

I have blocks of variables that I need to process. I put a list of the variable block names into a macro variable. Then I created a list of macro variables I want to pass the variable lists into. I am attempting to iterate through the list with this code but the only thing that is retained a macro variable called nextlist for the last block of variables. How can I get the SQL code to put the list of values into the macros I am feeding in from the &blockvarlist macro list?

I am running this in SAS 9.4

PROC SQL ; SELECT DISTINCT FINAL_NAME INTO :&next SEPARATED BY " " FROM 
metadata4b
                where varblockname = "&next2" and type = "Num" and 
                (index(final_name,"_NA") = 0 and index(final_name,"_1NA") 
     = 0 and index(final_name,"_2NA") = 0 ); QUIT;


%put &blocklist;
/*result of put: Q11 Q11_10N Q11_11N Q11_12N Q38_list*/
%put &blockvarlist;
/*result of put: Q11_10N_list Q11_11N_list Q11_12N_list Q38_list*/

%macro createarray;
    %global i nextblock;
        %do i = 1 %to %sysfunc(countw(&BLOCKLIST));
         %let nextblock = %scan(&BLOCKLIST, &i, %str( ));
        %let nextlist = %scan(&BLOCKVARLIST, &i, %str( ));
        PROC SQL; SELECT DISTINCT FINAL_NAME INTO :nextlist SEPARATED BY 
 " " FROM metadata4b
                where varblockname = "&nextblock" and type = "Num" and 
                (index(final_name,"_NA") = 0 and index(final_name,"_1NA") 
= 0 and index(final_name,"_2NA") = 0 ); QUIT;       
        %end;
%mend;

%createarray;

I want to generate a series of macro variables that I can reference later. For example Q38_list would contain the list of variables in that question block: Q38_CIR Q38_FRM Q38_OTR Q38_SCR

Are you familiar with variable shortcuts? You can use Q38: to refer to all variables that start with Q38. — Reeza
Why does the list of blocks (&BLOCKLIST) have 5 entries and the list of macro variable names (&BLOCKVARLIST) only have 4 entries? — Tom
Why are you putting the list of variable names into macro variables to begin with? It looks like you already have the list of variable names in a dataset. Why not just use the dataset for whatever purpose you intend to use the macro variables. Then number of variables per "block" will not be limited by the maximum length of a macro variable. — Tom

Richard Richard · Accepted Answer · 2019-08-26T21:21:10

I think you may need to resolve the nextlist macro variable during the INTO. You might also need to globalize the target prior to the SQL

%let nextlist = %scan(&BLOCKVARLIST, &i, %str( ));
%global &nextlist;

Change

SELECT DISTINCT FINAL_NAME INTO :nextlist

to

SELECT DISTINCT FINAL_NAME INTO :&nextlist

Using SQL to pass lists of values into macro variables in SAS

1 Answers