SAS Select Into Dynamic Naming

Question

I am trying to use SAS proc sql select into for 8 different scenarios.

  %MACRO Loop();
     proc sql;
         %do i=1 %to 8;
         select total_value
         into :Sol_&i. separated by ','
         from Master.adjusted_hours
         where Solicitation = "&i.";
    %end;
quit;
%mend;

%Loop();

However when I use the %put function, the macro variable isn't recognized. The error is "Apparent symbolic reference SOL_1 not resolved."

  %put &Sol_1;

How can I store the value in this macro variable and call it later in the code?

Ken Edmonds Ken Edmonds · Accepted Answer · 2020-07-10T00:50:58

You need to declare SOL_1 SOL_2, etc as global macro variables. I wasn't sure what was in your data, so I created some dummy data.

%global SOL_1 SOL_2 SOL_3 SOL_4 SOL_5 SOL_6 SOL_7 SOL_8;

data adjusted_hours;
    do x = 1 to 8;
        solicitation=put(x, 1.);
        do total_value = 1 to 10;
            output;
        end;
    end;
    drop x;
run;

%MACRO Loop();
 
     proc sql noprint;
     %do i = 1 %to 8;
         select total_value
         into : Sol_&i. separated by ','
         from adjusted_hours
         where Solicitation = "&i.";
     %end;
    quit;

%mend;

%Loop();

%put _USER_;

Partial log:
 
 GLOBAL SOL_1 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_2 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_3 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_4 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_5 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_6 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_7 1,2,3,4,5,6,7,8,9,10
 GLOBAL SOL_8 1,2,3,4,5,6,7,8,9,10

If you want to avoid the macro altogether and having to declare the %global variables, here's a data step solution that will work as long as your dataset is sorted by Solicitation.

data _null_;
    set adjusted_hours;
    by solicitation;
    format temp $50.;
    retain temp ;
    temp=CATX(',',temp, total_value);
    
    if last.solicitation then do;
        call symputx(CATS('SOL_', solicitation), temp);
        call missing(temp);
    end;
run;

SAS Select Into Dynamic Naming

1 Answers