How to loop over a proc in SAS?

Question

I would like to "automate" PROC calls in SAS using lists and loops. For example I would like to call PROC EXPORT with a list of calculation result tables such as:

do over (tables=FitStatistics Type3_Tests LSM Diff) ;
   PROC EXPORT DATA= WORK.tables
   OUTFILE="file.xls"
   DBMS=EXCEL REPLACE;
   SHEET=tables;
   RUN;
loop;

This should create a "file.xls" and insert a sheet for each table in "tables". I know that this should work with macros somehow, but couldn't figure the correct syntax.

Answer 1

I found a solution myself though it's not using pure SAS capabilities alone. If someone proposes a simpler solution I will upvote and accept it as final answer.

There is a SAS conference article by Jim Anderson who tackles this issue with his own for loop macro. Given this macro we can do

%for(tables, values=FitStatistics Type3_Tests LSM Diff, do= %nrstr( 
    PROC EXPORT DATA= WORK.&tables
    OUTFILE="file.xls"
    DBMS=EXCEL REPLACE;
    SHEET=&tables;
    RUN;
))

which is exactly what I had in mind. Just to avoid dead link issues, I'm quoting the macro given in his article here. It works but is quite obscure to me.

%macro for(macro_var_list,data=,values=,array=,to=,from=1,by=1,do=,delim=%str( 
),length=); 
  %global _for_loop_gen; 
  %if &_for_loop_gen=%str( ) %then %let _for_loop_gen=0; 
  %local _for_loop_itid _for_loop_ct _for_loop_code _for_loop_i  
         _for_loop_val1 _for_loop_var1 _n_ _for_loop_set  
         _for_loop_arrays _for_loop_values _for_loop_to 
         _for_loop_var_num; 
  %let _for_loop_set=%length(&data); 
  %let _for_loop_arrays=%length(&array); 
  %let _for_loop_values=%length(&values); 
  %let _for_loop_to=%length(&to); 
  %if (&_for_loop_set>0)+(&_for_loop_arrays>0)+ 
      (&_for_loop_values>0)+(&_for_loop_to>0)>1 %then 
  %do; 
    %put ERROR: "for" macro only one of "data=", "to=", "values=" or "array=" allowed; 
    %return; 
  %end; 
  %let _for_loop_code=&do; 
  %if %eval(%index(&do,%nrstr(%if))+%index(&do,%nrstr(%do))) %then 
  %do; %* conditional macro code - need to embed in macro; 
    %let _for_loop_gen=%eval(&_for_loop_gen+1); 
    %unquote(%nrstr(%macro) _for_loop_&_for_loop_gen(); &do %nrstr(%mend;)) 
    %let _for_loop_code=%nrstr(%_for_loop_)&_for_loop_gen(); 
  %end; 
  %let _for_loop_ct=0; 
  %if &_for_loop_set %then 
  %do; %* loop over dataset; 
         %let _for_loop_itid=%sysfunc(open(&data)); 
         %if &_for_loop_itid=0 %then 
         %do; 
           %put ERROR: cant open dataset data=&data; 
           %return; 
         %end; 
    %do %while(%sysfunc(fetch(&_for_loop_itid,NOSET))>=0); 
      %let _for_loop_ct=%eval(&_for_loop_ct+1); 
      %let _n_=&_for_loop_ct; 
       %let _for_loop_i=1; 
       %let _for_loop_var1=%scan(&macro_var_list,1,%str( )); 
       %do %while(%str(&_for_loop_var1) ne %str( )); 
         %let _for_loop_var_num=%sysfunc(varnum(&_for_loop_itid,&_for_loop_var1)); 
         %if &_for_loop_var_num=0 %then 
         %do; 
           %put ERROR: "&_for_loop_var1" is not a dataset variable; 
           %return;  
         %end; 
         %if %sysfunc(vartype(&_for_loop_itid,&_for_loop_var_num))=C %then 
         %do; %* character variable; 
           %let _for_loop_val1=%qsysfunc(getvarc(&_for_loop_itid,&_for_loop_var_num)); 
         %if %sysfunc(prxmatch("[^\w\s.]+",&_for_loop_val1)) %then 
           %let &_for_loop_var1=%qtrim(&_for_loop_val1); 
         %else 
           %let &_for_loop_var1=%trim(&_for_loop_val1); 
         %end; 
         %else 
         %do; %* numeric variable; 
           %let &_for_loop_var1=%sysfunc(getvarn(&_for_loop_itid,&_for_loop_var_num)); 
         %end; 
         %let _for_loop_i=%eval(&_for_loop_i+1); 
         %let _for_loop_var1=%scan(&macro_var_list,&_for_loop_i,%str( )); 
       %end; 
%unquote(&_for_loop_code) 
    %end; 
         %let _for_loop_i=%sysfunc(close(&_for_loop_itid)); 
    %return; 
  %end; 
  %else %if &_for_loop_arrays %then 
  %do; %* loop over one or more arrays; 
    %local _for_loop_arrays _for_loop_array1 _for_loop_len; 
    %if &macro_var_list=%str( ) %then %let macro_var_list=&array; 
    %let _for_loop_arrays=&array; 
    %let _for_loop_array1=%scan(&array,1,%str( )); 
    %if &length ne %str( ) %then  
      %let _for_loop_len=&length; 
    %else 
    %do; %* getnumber of iterations from first macro array; 
      %if %symexist(&_for_loop_array1.n) %then 
        %let _for_loop_len=&&&_for_loop_array1.n; 
      %else 
      %do; 
        %put ERROR: "for" macro for arrays needs "length=" argument; 
        %return; 
      %end; 
    %end; 
    %do _for_loop_ct=1 %to &_for_loop_len; 
      %let _n_=&_for_loop_ct; 
       %let _for_loop_i=1; 
       %let _for_loop_var1=%scan(&macro_var_list,1,%str( )); 
       %do %while(%str(&_for_loop_var1) ne %str( )); 
         %let _for_loop_array1=%scan(&_for_loop_arrays,&_for_loop_i,%str( )); 
         %if &_for_loop_array1=%str( ) %then 
         %do; %* more variables than arrays; 
             %put ERROR: "for" macro has more variables than arrays; 
          %return; 
         %end; 
         %let _for_loop_val1=%superq(&_for_loop_array1&_n_); 
         %if %sysfunc(prxmatch("[^\w\s.]+",&_for_loop_val1)) %then 
           %let &_for_loop_var1=%qtrim(&_for_loop_val1); 
         %else 
           %let &_for_loop_var1=%trim(&_for_loop_val1); 
         %let _for_loop_i=%eval(&_for_loop_i+1); 
         %let _for_loop_var1=%scan(&macro_var_list,&_for_loop_i,%str( )); 
       %end; 
%unquote(&_for_loop_code) 
    %end; 
    %return; 
  %end; 
  %else %if &_for_loop_values ne 0 %then 
  %do; %* loop over list of values; 
    %local _for_value_index _for_loop_values _for_loop_delim _for_loop_extra; 
    %let _for_loop_values=&values; 
    %let _for_loop_delim=&delim; 
    %let _for_value_index=1; 
    %if &macro_var_list=%str( ) %then 
    %do; %*empty variable list - perhaps (s)he just wants &_n_; 
      %let macro_var_list=_for_loop_extra; 
    %end; 
    %do %while(1); 
      %let _for_loop_ct=%eval(&_for_loop_ct+1); 
      %let _n_=&_for_loop_ct; 
       %let _for_loop_i=1; 
       %let _for_loop_var1=%scan(&macro_var_list,1,%str( )); 
       %do %while(%str(&_for_loop_var1) ne %str( )); 
         %let _for_loop_val1=%scan(&_for_loop_values,&_for_value_index,&_for_loop_delim); 
         %let _for_value_index=%eval(&_for_value_index+1); 
         %if %length(&_for_loop_val1)=0 %then 
         %do; %* end of values before end of variables, terminate iteration; 
          %return; 
         %end; 
         %let &_for_loop_var1=&_for_loop_val1; 
         %let _for_loop_i=%eval(&_for_loop_i+1); 
         %let _for_loop_var1=%scan(&macro_var_list,&_for_loop_i,%str( )); 
                %end; 
%unquote(&_for_loop_code) 
    %end; 
  %end; 
  %else %if &_for_loop_to %then 
  %do; %* loop from &from to &to by &by; 
    %*local &macro_var_list; 
    %let _for_loop_var1=%scan(&macro_var_list,1,%str( )); 
    %let _for_loop_ct=1; 
    %do _for_loop_i=&from %to %eval(&to) %by &by; 
      %let _n_=&_for_loop_ct; 
       %if %str(&_for_loop_var1) ne %str( ) %then %let &_for_loop_var1=&_for_loop_i; 
       %let _for_loop_ct=%eval(&_for_loop_ct+1); 
%unquote(&_for_loop_code) 
    %end; 
    %return; 
  %end; 
  %put ERROR: for macro requires a "data", "values", "to" or "array" keyword; 
%mend for; 

Answer 2

Only because your solution is incredibly long...use a macro loop. See the SAS 9.4 Macro documentation. The appendix has several code examples.

It's a straightforward macro loop that uses COUNTW to determine the number of terms and create the macro variable SHEET to hold the current value of the list.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1qvxz5u3uru7yn1nk7q64ohvwak.htm

 %macro iterm(lst);
  %let finish=%sysfunc(countw(&lst));
   %do i = 1 %to &finish;
             %let sheet= %scan(&lst,&i);
  PROC EXPORT DATA= WORK.&sheet OUTFILE="file.xls" DBMS=EXCEL REPLACE;
     SHEET=&sheet;
     RUN;

   %end;
 %mend iterm;

  %iterm(LSM Diff);

Answer 3

You can use the XLSX engine and PROC COPY.

libname out xlsx 'file.xlsx';
proc copy inlib=work outlibe=out ;
   select FitStatistics Type3_Tests LSM Diff ;
run;
libname out;