Some very common questions we receive into Technical Support are, “I need to be able to send the data for every hospital listed in my data set to a separate .csv file. How can I do that?” Or, “How can I run a PROC REPORT for each region listed in my data set?” And, “How can I send an email to each manager listed in my data set?”

Each one of these questions has something in common. Each question is asking how to execute a task based on a data set variable. Within SAS, the best way to execute a repetitive task is to use the SAS Macro language. The best way to invoke a macro based on a data set variable is to use CALL EXECUTE. The two most important aspects of CALL EXECUTE are that it allows you to:

• conditionally execute a macro based on DATA step logic
• pass in a data set variable’s value in as the value to a macro parameter

Two common errors when invoking macro statements based on DATA step logic
Because CALL EXECUTE is a DATA step call routine, it can be conditionally executed using DATA step logic. It is important to understand the basic timing of a macro statement. A macro statement executes a DATA step at compile time. This means that macro code executes before any DATA step logic is ever evaluated. Here is an example of logic that will not work. Because of the timing of macro statements, this macro invocation will always execute, regardless of the IF condition. CALL EXECUTE can be used to accommodate for this difference in timing.

If first.hospital_id then do;
  %mymacro(hospital_id)
End;

The above example also illustrates another common mistake when attempting to invoke a macro based on DATA step logic. This example is attempting to pass in the value of the data set variable HOSPITAL_ID as the parameter to the macro. It is important to know that the macro language is simply a text language. When the MYMACRO macro executes, the text ‘hospital_id’ is passed in as the value of the macro parameter, not the value of the data set variable HOSPITAL_ID. Again the desired result can be achieved using CALL EXECUTE.

How to invoke a macro using CALL EXECUTE
So now let’s see the correct way to invoke the macro using CALL EXECUTE. In the following example, we are going to dynamically create a .csv file for each hospital listed in our data set. This will be accomplished by placing a PROC EXPORT within a macro. CALL EXECUTE will allow to invoke the macro for each hospital listed in the HOSPITALS data set.

/* sample data set that contains the hospital ids */ 
data hospitals;                                                
  input hospital_id $ quarter number_of_patients;                               
  datalines;                                      
A100 1 125                                    
A100 2 115                                                                                                                              
A100 3 130                                                                                                                              
A100 4 110                                                                                                                              
A200 1 200                                                                        
A200 2 195 
A200 3 180                                                        
A200 4 190                                       
;                                                

/* this macro uses PROC EXPORT to write data to a .csv file */

%macro mymacro(hosp_id);                                                                                                                

proc export data=hospitals(where=(hospital_id="&hosp_id"))                                                                                  
  outfile="c:\hospitals\&hosp_id..csv"                                                                                                  
  dbms=csv                                                                                                                              
  replace;                                                                                                                              
run;                                                                                                                                    

%mend;                                                                                                                                  

data _null_;                                                                                                                            
  set hospitals;                                                                                                                        
  by hospital_id;

/* this logic allows us to call the MYMACRO macro 
each time the value of HOSPITAL_ID changes */   

  if first.hospital_id then do;
    call execute(cats('%mymacro(',hospital_id,');')); 
  end;                                                                  
run;

This logic will work regardless of the number of hospitals listed and of the number of quarters listed for each hospital. When this code is executed, the following macro invocations are generated.

First time the IF condition is true:
%mymacro(A100)
Second time IF condition is true:
%mymacro(A200)

How the CALL EXECUTE routine processes macros
It is important to know what happens behind the scenes when using CALL EXECUTE. When the argument to CALL EXECUTE is a macro invocation, the macro will execute immediately. This means that all macro statements are executed and all macro variable references are resolved. All non-macro code generated by the macro is stored and does not execute until after the step boundary that terminates the DATA step that contains the CALL EXECUTE is encountered. The timing of when statements are executed can sometimes lead to unresolved macro variables, which in turn can lead to unexpected syntax errors. This issue is discussed in detail in SAS Usage Note 23134. The note discusses the cause of the potential problem as well as the solution.

CALL EXECUTE is an invaluable tool that should be added to your macro programming toolbox. CALL EXECUTE allows you to make your macro invocations data driven, which can make your programming more efficient and adaptable.