selectsasprocselect-into

SAS Select Into Dynamic Naming


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?


Solution

  • 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;