The SAS way to loop over a table outside a data step

Question

I am wondering what is the cleanest way how to perform a macro loop over a data table outside a data step in order to e.g. read in files from the table have and do some complex analysis for each of the files.

Assume we have a table have containing a set of file names and other meta data:

N  filename  purpose
1  foo.xls   Blue team data
2  bar.xls   Read team data

I was thinking of something like

%local lines current_file current_purpose;

proc sql noprint;
   select count(*) into: lines from have;
quit;

%do I=1 %to &lines.;
   %put --- Process file number &I. ---;
   data _null_;
      set have;
      if _n_=&I. then do;
        call symput('current_file',filename);
        call symput('current_purpose',purpose);
      end;
   run;
   %put --- &current_file. contains &purpose.;
   /* Here comes the actual analysis */ 
%end;

Is this the way how to do it? For me, this does not look like the simplest way.

Related questions:

• SAS loop through datasets
• SAS let statement: refer to a cell value?

Answer 1

So if you defined a macro name ANALYSIS with input parameters FILENAME and PURPOSE.

%macro analysis(filename,purpose);
  /* Here comes the actual analysis */ 
  title &purpose ;
  proc import datafile="&filename" ....
%mend;

Then you can use a data step to generate one call to the macro for each observation. You can use CALL EXECUTE, but I find it clearer and easier to debug to just write the code to a file and then %INCLUDE it. Especially when the parameter name matches the variable name in the metadata being used to drive the code generation.

So this step :

filename code temp;
data _null_;
   set have;
   file code;
   put '%analysis(' filename= ',' purpose= :$quote. ')' ;
run;

Will generate a program like:

%analysis(filename=foo.xls,purpose="Blue team data")
%analysis(filename=bar.xls,purpose="Red team data")

Which you can then run using

%include code / source2;