Master Program, Loop Process

[theponcer]

I frequently create program processes using a master file that allow the user to define inputs for several programs and then run those programs sequentially. For a simple example, let's say that I had two programs that I wanted to run. Program (1) creates the data, and Program (2) summarizes it. I would then create a master file, Program (0), that contains the inputs for both programs and allows the user to run both. Each program is in a separate program file. Here is what it looks like:

*Master File;
%include "\\XXX\SAS Question\Pre-Defined Macros.sas";

%let program_path = \\XXX\SAS Question;

*Program 1 Inputs;
%let run1 = Y;
%let path1 = &program_path\(1) Create Data.sas;

*Program 2 Inputs;
%let run2 = Y;
%let path2 = &program_path.\(2) Summarize Data.sas;
%let crop_data_table = sum_table_veggies;
%let sum_vars = num;
%let by_vars = vegetable;
%let output_table = vegetable_summary;


%if &run1. = Y %then %do; %include "&path1."; run; %end; 
%if &run2. = Y %then %do; %include "&path2."; run; %end; 

***END OF MASTER FILE***;

***START OF PROGRAM (1)***;
data sum_table_veggies;
   infile datalines delimiter=','; 
   input vegetable $ num 8.;
   datalines;                      
Carrot, 9
Rutabega, 22
Parsley, 98
Carrot, 13
Rutabega, 5
Parsley, 56
;

data sum_table_fruits;
   infile datalines delimiter=','; 
   length fruit $10.;
   input fruit $ num 8.;

   datalines;                      
Cantaloupe, 9
Raspberry, 22
Peach, 98
Cantaloupe, 13
Raspberry, 5
Peach, 56
;
***END OF PROGRAM (1)***;

***START OF PROGRAM (2)***;

%macro summarize(in_table,sum_vars,class_vars,out_table);
proc summary data=&in_table. nway;
var &sum_vars.;
class &class_vars.;
output out=&out_table. (drop=_:) sum=;
quit;
%mend;

%summarize(&crop_data_table., &sum_vars., &by_vars., &output_table.);

***END OF PROGRAM (2)***;

Now, I want to run this process several times, passing the inputs to SAS programmatically. The way that I tried to do this is by macro-izing the master file itself, passing the inputs in as a data table, and using call execute to run the macro for as many observations as are in the inputs data table, as follows:

*Master Loop;

%include "XXX\SAS Question\Pre-Defined Macros.sas";
%let Path = your_path_here;
*Create inputs table (normally I would just import this from Excel);
%global crop_data_table sum_vars by_vars output_table;

data inputs;
   infile datalines delimiter=','; 
   length var1 $121 var2 $1 var3 $20 var4 $1 var5 $22 var6 $17 var7 $3 var8 $9 var9 $17;
   input var1 $ var2 $ var3 $ var4$ var5 $ var6 $ var7 $ var8 $ var9 $;

   datalines;                      
&Path.\SAS Question, Y,(1) Create Data.sas,Y,(2) Summarize Data.sas,sum_table_veggies,num,vegetable,vegetable_summary
&Path.\SAS Question, Y,(1) Create Data.sas,Y,(2) Summarize Data.sas,sum_table_fruits,num,fruit,fruit_summary

;

%include "&Path.\SAS Question\Loop Macro.sas";

*Use call execute to loop through the macro;
data _null_;
  length callstr $350.;
  set inputs (obs=max);
  callstr = cats('%loop(var1=',var1,',var2=',var2,',var3=',var3,',var4=',var4,',var5=',var5,'
  ,var6=',var6,',var7=',var7,',var8=',var8,',var9=',var9,')');
  call execute(callstr);

run;

***END OF MASTER LOOP***;

***START OF LOOP MACRO***;
*Loop Macro;
%macro loop(var1, var2, var3, var4, var5, var6, var7, var8, var9);
%let program_path = &var1.;

%let run1 = &var2.;
%let path1 = &var1.\&var3.;

%let run2 = &var4.;
%let path2 = &var1.\&var5.;
%let crop_data_table = &var6.;
%let sum_vars = &var7.;
%let by_vars = &var8.;
%let output_table = &var9.;


%if &var2. = Y %then %do; %include "&var1.\&var3."; run; %end; 
%if &var4. = Y %then %do; %include "&var1.\&var5."; run; %end; 
  %put _all_;
%mend;
***END OF LOOP MACRO***;

However, when I run this series of programs, I get the following output tables:

• Work.Inputs
• Work.Sum_Table_Veggies
• Work.Sum_Table_Fruits
• Work.Fruit_Summary

I should also be creating the Work.Vegetable_Summary table (see obs1, var9). I am able to create this table if I pass only the first variable of the inputs table to the call execute statement (obs=1). However, it does not get created when I set the entire table (obs=max). My questions are:

1. Does my explanation of this process make sense/is it sufficient?
2. Why isn't the work.vegetable_summary table being created?
3. Is there a better/simpler/more efficient approach to pass a set of inputs to and run the master file loop several times? 

I'm hoping this isn't too confusing - if it is, feel free to ask more questions and I will explain as best I can. Thanks for your patience and for your help! 

[RichardDeVen · Accepted solution]

call execute performs as much complete macro processing as possible while the data step is running and then queues up any macro gen that introduces a proc or data step.  You can prevent this behavior and queue all the macro invocations for sequential submission after the DATA step by wrapping execute submitted code in %NRSTR

Change

 call execute(callstr);

to

 call execute(cats(
    '%NRSTR(',
      callstr,
    ')'
));

[Tom]

There is a timing issue with CALL EXECUTE and macro execution.    The timing issue happens when the macro being called has logic that determines what code to run based on values that are changed by the earlier SAS statements generated by the macro.  For example by referencing a macro variable that is modified/created by a CALL SYMPUTX() statement in a data step or an INTO clause in a PROC SQL step.  This is because the macro will actually run while the CALL EXECUTE statement is running.  But the actual SAS code it generates will be pushed onto the stack to run after the data step finishes.  So any decision about what code to generate happens before the generated code has a chance to actually run.

Your posted examples don't look like they will actually have such issues, but it can't hurt to try the fix.  If nothing else it will at least make your SAS log easier to read.  The fix is to wrap the macro call in %NRSTR() macro function.  That way the macro does NOT run while the code is pushed via CALL EXECUTE(), but it will run when it is pulled back off after the data step.

  callstr = cats('%nrstr(%loop)(var1=',var1,',var2=',var2,',var3=',var3,',var4=',var4,',var5=',var5,'
  ,var6=',var6,',var7=',var7,',var8=',var8,',var9=',var9,')');

Or just skip the CALL EXECUTE and just write the generated code a text file instead and %INCLUDE it .  Then you can take advantage of the fact that your dataset variable names match the macro's parameter names.

* Write macro calls to temporary code file;
filename code temp;
data _null_;
  set inputs (obs=max);
  file code ;
  put '%loop(' var1= (var2-var9) (= ',') ')';
run;
%include code ;