CODEWITHSUNDEEP
sqlsassas-macro
ljaset
ljaset

Reputation: 1

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

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

Upvotes: 0

Views: 661

Answers (1)

Richard
Richard

Reputation: 27518

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

Upvotes: 1

Related Questions