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:
Second time IF condition is true:
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.
Hi Kevin, thank you for your help. For the above example, how would one send an email to, say, each manager of the hospital_id and attach their respective file? I was able to PROC EXPORT based on hospital_id and am unsure how to approach the email code. Your expertise is appreciated!
Thank you for the elaborate description of the process. Could you please tell me how would the process change if there was a unique hospital name associated with a unique hospital id and if I need to include the respective hospital name into my file name?
For ex. suppose name of the A100 is id for hospital St. Luke's Philadelphia how can i get a file named A100_St. Luke's Philadelphia?
Looking forward to your reply.
To accomplish the task you mentioned you just need to add the hospital name as another parameter. I have modified the code, see below:
input name $ hospital_id $ quarter number_of_patients;
St.Luke A100 1 125
St.Luke A100 2 115
St.Luke A100 3 130
Rex A200 1 200
Rex A200 2 195
Rex A200 3 180
Rex A200 4 190
/* this macro uses PROC EXPORT to write data to a .csv file */
proc export data=hospitals(where=(hospital_id="&hosp_id"))
/* this logic allows us to call the MYMACRO macro
each time the value of HOSPITAL_ID changes */
if first.hospital_id then do;
Hi Kevin, very nice introduction to data-driven macro invocations! To complement this post, may I refer your readers to my recent blog post CALL EXECUTE made easy for SAS data-driven programming, which they might find useful for exploring CALL EXECUTE in greater detail.