In my prior posts (Data-driven SAS macro loops, Modifying variable attributes in all datasets of a SAS library, Automating the loading of multiple database tables into SAS tables), I presented various data-driven applications using SAS macro loops.
However, macro loops are not the only tools available in SAS for developing data-driven programs.
CALL EXECUTE is one of them. The CALL EXECUTE routine accepts a single argument that is a character string or character expression. The character expression is usually a concatenation of strings containing SAS code elements to be executed after they have been resolved. Components of the argument expression can be character constants, data step variables, macro variable reference, as well as macro references. CALL EXECUTE dynamically builds SAS code during DATA step iterations; that code executes after the DATA step’s completion outside its boundary. This makes a DATA step iterating through a driver table an effective SAS code generator similar to that of SAS macro loops.
However, the rather peculiar rules of the CALL EXECUTE argument resolution may make its usage somewhat confusing. Let’s straighten things out.
Argument string has no macro or macro variable reference
If an argument string to the CALL EXECUTE contains SAS code without any macro or macro variable references, that code is simply pushed out (of the current DATA step) and appended to a queue after the current DATA step. As the DATA step iterates, the code is appended to the queue as many times as there are iterations of the DATA step. After the DATA step completes, the code in the queue gets executed in the order of its creation (First In First Out).
The beauty of this process is that the argument string can be a concatenation of character constants (in single or double quotes) and SAS variables which get substituted with their values by CALL EXECUTE for each DATA step iteration. This will produce data-driven, dynamically generated SAS code just like an iterative SAS macro loop.
Let’s consider the following example. Say we need to load multiple Oracle tables into SAS tables.
Step 1. Creating a driver table
In order to make our process data-driven, let’s first create a driver table containing a list of the table names that needed to be extracted and loaded:
/* create a list of tables to extract & load */ libname parmdl '/sas/data/parmdata'; data parmdl.tablelist; length tname $8; input tname; datalines; ADDRESS ACCOUNT BENEFIT FINANCE HOUSING ; |
This program runs just once to create the driver table parmdl.tablelist.
Step 2. Loading multiple tables
Then, you can use the following data-driven program that runs each time you need to reload Oracle tables into SAS:
/* source ORACLE library */ libname oralib oracle path="xxx" schema="yyy" user="uuu" PASSWORD="{SAS002}ABCDEFG12345678RTUR" access=readonly; /* target SAS library */ libname sasdl '/sas/data/appdata'; /* driver table SAS library */ libname parmdl '/sas/data/parmdata'; data _null_; set parmdl.tablelist; call execute(cats( 'data sasdl.',tname,';', 'set oralib.',tname,';', 'run;')); run; |
In order to concatenate the components of the CALL EXECUTE argument I used the cats() SAS function which returns a concatenated character string removing leading and trailing blanks.
When this program runs, the SAS log indicates that after the data _null_ step the following statements are added and executed:
NOTE: CALL EXECUTE generated line. 1 + data sasdl.ADDRESS;set oralib.ADDRESS;run; 2 + data sasdl.ACCOUNT;set oralib.ACCOUNT;run; 3 + data sasdl.BENEFIT;set oralib.BENEFIT;run; 4 + data sasdl.FINANCE;set oralib.FINANCE;run; 5 + data sasdl.HOUSING;set oralib.HOUSING;run; |
In this example we use data _null_ step to loop through the list of tables (parmdl.tablelist) and for each value of the tname column a new data step gets generated and executed after the data _null_ step. The following diagram illustrates the process:
Argument string has macro variable reference in double quotes
If an argument to the CALL EXECUTE has macro variable references in double quotes, they will be resolved by the SAS macro pre-processor during the DATA step compilation. Nothing unusual. For example, the following code will execute exactly as the above, and macro variable references &olib and &slib will be resolved to oralib and sasdl before CALL EXECUTE takes any actions:
%let olib = oralib; %let slib = sasdl; data _null_; set parmdl.tablelist; call execute ( "data &slib.."!!strip(tname)!!';'!! "set &olib.."!!strip(tname)!!';'!! 'run;' ); run; |
Argument string has macro or macro variable reference in single quotes
Here comes the interesting part. If the argument to CALL EXECUTE has macro or macro variable references in single quotes, they still will be resolved before the code is pushed out of the DATA step, but not by the SAS macro pre-processor during the DATA step compilation as it was in the case of double quotation marks. Macro or macro variable references in single quotes will be resolved by CALL EXECUTE during the DATA step execution. For example, the following code will execute exactly as the above, but macro variable references &olib and &slib will be resolved by CALL EXECUTE:
%let olib = oralib; %let slib = sasdl; data _null_; set parmdl.tablelist; call execute('data &slib..'!!strip(tname)!!';'!! 'set &olib..'!!strip(tname)!!';'!! 'run;' ); run; |
Timing considerations
CAUTION: If your macro contains some non-macro language constructs for assigning macro variables during run time, such as a CALL SYMPUT or SYMPUTX statement (in a DATA step) or an INTO clause (in PROC SQL), resolving those macro variable references by CALL EXECUTE will happen too soon, before your macro-generated code gets pushed out and executed. This will result in unresolved macro variables. Let’s run the following code that should extract Oracle tables into SAS tables as above, but also re-arrange column positions to be in alphabetical order:
%macro onetable (tblname); proc contents data=oralib.&tblname out=one(keep=name) noprint; run; proc sql noprint; select name into :varlist separated by ' ' from one; quit; %put &=varlist; data sasdl.&tblname; retain &varlist; set oralib.&tblname end=last nobs=n; if last then call symput('n',strip(put(n,best.))); run; %put Table &tblname has &n observations.; %mend onetable; data _null_; set parmdl.tablelist; call execute('%onetable('!!strip(tname)!!');'); run; |
Predictably, the SAS log will show unresolved macro variable references, such as:
WARNING: Apparent symbolic reference VARLIST not resolved. WARNING: Apparent symbolic reference N not resolved. Table ADDRESS has &n observations. |
SOLUTION: To avoid the timing issue when a macro reference gets resolved by CALL EXECUTE too soon, before macro variables have been assigned during macro-generated step execution, we can strip CALL EXECUTE of the macro resolution privilege. In order to do that, we can mask & and % characters using the %nrstr macro function, thus making CALL EXECUTE “macro-blind,” so it will push the macro code out without resolving it. In this case, macro resolution will happen after the DATA step where CALL EXECUTE resides. If an argument to CALL EXECUTE has a macro invocation, then including it in the %nrstr macro function is the way to go. The following code will run just fine:
data _null_; set parmdl.tablelist; call execute('%nrstr(%onetable('!!strip(tname)!!'));'); run; |
When this DATA step runs, the SAS log indicates that the following statements are added and executed:
NOTE: CALL EXECUTE generated line. 1 + %onetable(ADDRESS); 2 + %onetable(ACCOUNT); 3 + %onetable(BENEFIT); 4 + %onetable(FINANCE); 5 + %onetable(HOUSING); |
CALL EXECUTE argument is a SAS variable
The argument to CALL EXECUTE does not necessarily have to contain or be a character constant. It can be a SAS variable, a character variable to be precise. In this case, the behavior of CALL EXECUTE is the same as when the argument is a string in single quotes. It means that if a macro reference is part of the argument value it needs to be masked using the %nrstr() macro function in order to avoid the timing issue mentioned above.
In this case, the argument to the CALL EXECUTE may look like this:
arg = '%nrstr(%mymacro(parm1=VAL1,parm2=VAL2))'; call execute(arg); |
Making CALL EXECUTE totally data-driven
In the examples above we used the tablelist driver table to retrieve values for a single macro parameter for each data step iteration. However, we can use a driver table not only to dynamically assign values to one or more macro parameters, but also to control which macro to execute in each data step iteration. The following diagram illustrates the process of totally data-driven SAS program:
Conclusion
CALL EXECUTE is a powerful tool for developing dynamic data-driven SAS applications. Hopefully, this blog post demonstrates its benefits and clearly explains how to avoid its pitfalls and use it efficiently to your advantage. I welcome your comments, and would love to hear your experiences with CALL EXECUTE.
71 Comments
Thank you for explaining the compilation part in detail. I have tried different options calling macro using CALL EXECUTE like Single quotes/double quotes
but every time CALL EXECUTE is being triggered at the end of data step
Example:
input data iftp, variable ipdsn:
AAAAAAAAAAAAAA
BBBBBBBBBBBBBB
data _null_; set iftp; put ipdsn; CALL EXECUTE ('%INPS(ipdsn);'); put ipdsn; run; %macro INPS(ipdsn); data _NULL_; putlog ipdsn; run; %mend;Output is written as below
BBBBBBBBBBBBBBB
BBBBBBBBBBBBBBB
But the expected output should be
AAAAAAAAAAAAAAA
BBBBBBBBBBBBBBB
I want CALL EXECUTE to trigger macro every time in a loop, how can i do this? please advise
Hi Pratheep,
You cannot run your macro within a data step because your macro itself is a data step. In order to be run within a data step your macro must consist of macro statements, but it cannot be another data step. Or CALL EXECUTE can generate a code for each data step iteration and push it outside the data step for execution; in this case you should use %nrstr macro function as described in this post to defer the macro execution. Also, you incorrectly pass variable name as macro parameter; for correct usage see this example in the blog post:
data _null_; set parmdl.tablelist; call execute('%nrstr(%onetable('!!strip(tname)!!'));'); run;Hello Leo,
Thank you for your prompt response!
I have coded a sample program to pass the value from data step inside a macro variable (filea) . I can see the file name is being passed into the macro, but i m not sure how to get the value inside the macro variable and trim. could you please assist me on this?
%Macro INPS(ipdsn); data _null_; filea = "&ipdsn"; %put MACRO: ipdsn -->: &ipdsn; %put MACRO: ipdsn -->: filea; newHostDSN = trim(filea); run; %Mend; data _null_; files = 'AAAAAAAAAAAAAA,BBBBBBBBBBBBBB'; f_count = countw(files); do i=1 to f_count; file = scan(files, i, ','); put 'DATA STEP: ipdsn -->: ' file; CALL EXECUTE (cats( '%INPS(', file, ');' )); end; run;Output:
DATA STEP: ipdsn -->: AAAAAAAAAAAAAA
MACRO: ipdsn -->: AAAAAAAAAAAAAA
MACRO: ipdsn -->: filea
DATA STEP: ipdsn -->: BBBBBBBBBBBBBB
MACRO: ipdsn -->: BBBBBBBBBBBBBB
Hi Pratheep,
1) Do not use %PUT within your macro since it is in a data step, use just PUT statement:
2) You can trim a value within CALL EXECUTE when you pass parameter to your macro:
CALL EXECUTE (cats( '%INPS(', trim(file), ');' ));3) In the future, when you need help with your coding problems, please post your questions at SAS Communities - https://communities.sas.com/ .
Hope this helps.
Leonid, thank you very much. Now the code is working as expected.
Hi Leonid,
Many thanks for your post. Could you help me with one question about %NRSTR?
I got one tip from SAS help and listed it below. I knew the macro function %NRSTR would mask a macro statement during the execution of call execute routine when the argument was resolved by the macro processor. When the macro processor found the %NRSTR, it would mask what is included in the %NRSTR by delta character. So the '%sales('||month||')' would have leading delta character in input stack. When the data step was completed, the masked '%sales('||month||')' would be handled by macro processor again. Is it the process right? If so, will the macro processor unmark the '%sales('||month||')' automatically?
The following example uses the %NRSTR macro quoting function to mask the macro statement. This function will delay the execution of macro statements until after a step boundary.
call execute('%nrstr(%sales('||month||'))');
Hi Derek, posting here for Leonid (who may yet respond). I think your assessment is correct. Is that what you needed to know?
Thanks, Chris. My previous assessment might be incorrect. I thought the argument %NRSTR('%sales('||month||')') will be processed directly by macro processor before and the CALL EXECUTE only passes the argument to macro processor if this routine detects "&" or "%" in the argument.
Hi Derek,
%NRSTR macro function masks its argument during compilation of your programs and it masks % to prevent %sales macro execution within the data step. Therefore, CALL EXECUTE will push its resolved argument outside the data step; that resolved argument will not contain the %nrstr macro function anymore. The statement generated by the CALL EXECUTE will look like
%sales(10);and it will be executed on its own, after the data step. It does not require any unmasking, it's just a macro call.Hope this helps.
Hi Leonid,
Thanks for your reply. I try to learn SAS macro by myself recently and am really confused by macro compilation time, execution time, quotation, etc. Thanks to experts like you online to give me instructions.
You mentioned in the blog 'Macro or macro variable references in single quotes will be resolved by CALL EXECUTE itself.'. Does it mean these kinds of macros or macro variables will not be sent back to macro processor but processed by CALL EXECUTE directly? And then the resolved macros or macro variables are sent back to input stack.
Thank you for your feedback, Derek! The point here is that macro variable references in single quotes are resolved "not by the SAS macro pre-processor during the DATA step compilation" but by the CALL EXECUTE during DATA step execution. Whether CALL EXECUTE uses some macro pre-processor modules for that is not important. What important is that such macro variables resolution is controlled by CALL EXECUTE during execution, not by the usual program flow when macro pre-processor resolves un-quoted or double-quoted macro variable references during compilation. I adjusted the quoted sentence to make it more explicit. It now reads "Macro or macro variable references in single quotes will be resolved by CALL EXECUTE during the DATA step execution."
Thanks, Leonid. Your reply helps me a lot. I have another question about the input stack and CALL EXECUTE.
If I have two data steps blow in one program and submit it to run. When the CALL EXECUTE sends the '%sales(10);' in the input stack, the second data step of 'data b;' is still waiting in the input stack, or it has entered the word queue within the word scanner?
I guess 'data b;' is still waiting in the input stack, as the '%sales(10);' will be invoked immediately after the 'data a', but I can't find the details in what I have read.
data a; .... call execute('%nrstr(%sales('||month||'))'); .... run; data b; .... run;Hi Derek,
I also was not able to find this described explicitly, but I believe you are correct. The data step b will not be compiled (will be waiting in the input stack) until all the macro invocations '%sales' generated by the data step a will be executed. Only then data step b will be compiled and executed.
Hope this helps.
Thanks.
Thanks for your generosity, Leonid. It's much appreciated.
You are very welcome, John! Thank you for your feedback.
Hi Leonid,
This is a simplified scenario, where in production environment where many more complex calculations will be involved. From users' perspective, they are expecting the end result at the end of the macro to be displayed and shown on screen visually. Moving forward, what would be the approach to educate the user "no, you will not be able to see the calculated result immediately, because of...".
Hi Xooi, I did not say "no, you will not be able to...", what I said was the method you chose for your problem did not fit to your problem. You still do not describe your problem in its entirety but rather provide some approach to a potential solution. In your case, if you create a macro function that contains only macro language (no SAS language steps), you can achieve what your code is failing to achieve. For example, run this:
However, I am not sure whether your code is representative of your problem. It is still unclear why you use global macro variable. It seems that you are trying so solve the wrong problem. You need to describe your problem in its entirety in order for me to be able to help you.
Hi Leonid,
Basically, I'm writing a custom macro name double_it, where it will parse in one parameter and multiply by 2,and store the result to global variable using symput.
during the following data step example, I created 3 variables, namely before_1, before_2 and before_3 with the value 228, 1404, and 999 respectively, and execute the the macro using nrstr.
However, when I the after_1 after_2 and after_3 all showing the same initial value of 123.I have double checked the log, where it's show the correct value of 456, 2808, and 1998. In other words, the macro executed correctly but the screen is showing otherwise.
In short, what is the proper way to ensure the outcome of value is showing as expected and not 123?
Hi Xooi, as I described in this blog post, "CALL EXECUTE dynamically builds SAS code during DATA step iterations; that code executes after the DATA step’s completion outside its boundary. That means that the result of your macro %double_it, &global_result is not available within the data step abc where you try using it; at this point it is still equal to its initial value 123. Therefore, call execute is not a solution to what you are trying to do.
It is not clear to me why you would need to over-complicate your problem with a macro and call execute to double a value, you can simply do it by multiplying by 2:
after_1 = 2*before_1;.Dear Leonid,
I am currently facing a problem where the results in data step does not behave as expected. What is the proper way to solve it and below is the code:
LIBNAME SLIBCOMN BASE "F:\SLIB\COMMON"; %global global_result; %let global_result = 123; %macro double_it(in_); %local in; %let in = &in_; data mc_double_it; a = ∈ result = a * 2; call symput('global_result', result); run; %put double_it result after run = &global_result; %mend double_it; DATA abc; initial_value = SYMGET('global_result'); before_1 = 228; arg_1 = cats('%nrstr(%double_it(in_=', before_1, '))'); call execute(arg_1); after_1 = &global_result; before_2 = 1404; arg_2 = cats('%nrstr(%double_it(in_=', before_2, '))'); call execute(arg_2); after_2 = &global_result; before_3 = 999; arg_3 = cats('%nrstr(%double_it(in_=', before_3, '))'); call execute(arg_3); after_3 = &global_result; *check9 = "&global_result"; *after = &global_result; RUN;Looking forward to hear from you, thanks.
Could you please briefly describe what you are trying to do? What data step behavior do you expect?
A couple of points.
It is not necessary to add the %NRSTR() around all of the text being pushed with CALL EXECUTE(). Just the parts you want the macro process to ignore until the code is pulled back to run. So just wrap the macro name itself:
call execute('%nrstr(%onetable)('!!strip(tname)!!');');And to the side discussion about using PUT() with dynamic format specification you need to use the PUTN(), or PUTC() for character formats, instead. Those functions receive the format specification as an expression to be resolved at execution time instead of as constant text to be evaluated at compile time.
Thank you, Tom, for your comment.
Agree, it's not necessary to apply %NRSTR() function to the whole argument of the CALL EXECUTE, you can apply it to the macro name only. Moreover, it is not necessary to apply %NRSTR() function to the whole macro name, you can apply it to the % sign only (you would just need to double it to be %%). So your code snippet can be re-written as
call execute('%nrstr(%%)onetable('!!strip(tname)!!');');.Or you can get fancy and apply %NRSTR() function to a partial macro name, e.g.
call execute('%nrstr(%o)netable('!!strip(tname)!!');');. 🙂I guess, it's a matter of personal preference what part of the argument to wrap into %NRSTR, but your point is well taken as it helps better understanding how this works.
An update, when I change line 7 to the following, it does work, so without the 'if eof ', the call execute is generating too many statements, somehow that is causing the error in the log, do you have any other explanations for the error?
7. if eof then call execute(vname(ikdcc(i))||"=put("||vname(ikdc(i))||","||vname(ikdc(i))||"_.);");
Hi Fang, unfortunately, I can't help you debugging this code here as
1) you are not providing your bigger picture (description of what you are trying to achieve);
2) you are not providing the whole code (e.g. I don't see your arrays definitions. What else don't I see?);
3) you say that now your line 7 works, but absence of ERROR in the SAS log does not mean that your code is logically correct and works properly;
4) As I suggested above this is not the right forum for such an assistance (see SAS Support Communities).
Thank you Leonid, I understand and I am good now 🙂
Great, I am glad you figured this out.
Hi Leonid,
I was trying to use call execute in an array, apparently, this doesn't work, please see below, there are three arrays, ikdc is the array of original numeric vars with formats, ikdcn is the array of numeric variables that will be a copy of ikdc without the format, ikdcc is the array that will store the character values of the formatted values of the original ikdc vars.
1. do i=1 to dim(ikdcc);
2. ikdcn(i)=ikdc(i);
3. formt=vformat(ikdc(i));
4. if eof then put "formt=" formt;
5. *ikdcc(i)=put(ikdc(i),vformat(ikdc(i))); ---This does not work.
6. /* call execute(vname(ikdcc(i))||"=put("||vname(ikdc(i))||","||vformat(ikdc(i))||");");*/
7. if ikdc(i) ne . then call execute(vname(ikdcc(i))||"=put("||vname(ikdc(i))||","||vname(ikdc(i))||"_.);");
8. if eof then call execute("label "||vname(ikdcn(i))||'="'||strip(vlabel(ikdc(i)))||'";');
9. if eof then call execute("label "||vname(ikdcc(i))||'="'||strip(vlabel(ikdc(i)))||'";');
10. end;
line 5 does not work, apparently, formats cannot be expressions??
line 7 produce the following statements in the log: is there a way to make this work?
"NOTE: Line generated by the CALL EXECUTE routine.
363 + ikdc_c_6=put(ikdc_skef_sc_6,ikdc_skef_sc_6_.);
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order."
Thank you!
Hi Fang,
Line 5: when using put function, the second argument must be the name of a format with a period and optional width and decimal specifications, not a character constant, variable, or expression. (See PUT function documentation).
Line 7: I am not sure why you use CALL EXECUTE. Do you realize that CALL EXECUTE generates SAS code that gets pushed out and executed outside the data step? If not, please re-read this post carefully. Your CALL EXECUTE generates statement ikdc_c_6=put(ikdc_skef_sc_6,ikdc_skef_sc_6_.); which is invalid statement outside a data step.
If you want help with your problem, put it on the SAS Support Communities - please provide your whole problem description, data sample - there might be better ways of solving your problem than the coding approach your are using.
I realize that, i.e., the statements generated get executed after the data step. I was just trying to use array and assign the format associated with each element in the array, I can use a macro loop approach to do it. Just curious whether call execute can accomplish the same thing.
I do have these two statements before line 1:
"data ikdc;
set ikdc(drop=redcap_repeat_instance redcap_repeat_instrument redcap_survey_identifier) end=eof;
if eof then call execute('data &syslast.; set &syslast.;'); "
And I have this statement after line 7:
" if eof then call execute ('run;'); "
So I am trying to run the statements generated by call execute in a separate data step, that is why line 8 and line 9 works, why not line 7?
timely Post Leonid, I was just trying to create a macro to correct student assignments. I have 41 datasets that are named Firstname_Lastname_final.sas7bdat submitted by students that I saved in a library. I want to compare each of them with my control dataset to confirm that students got the right results. Would you be able to suggest a simple way to use & understand call execute for my business problem(macro newbie here 🙂 thank you in advance.
Sure Charu, I'd be happy to help. Please send me a detail description of what you are trying to do.
Hi Charu, based on the detailed description provided, you can achieve what you need in a single data step (no macro is needed):
/* PROBLEM DESCRIPTION: *control data set library assignment; %let path =U:\Schulich MBAN 2000\assignments\7-6-2020_Module_9_Quiz\A1 case study; libname A1 "&path"; *student dataset library assignment; libname mbanA1 "&path\student datasets\student datasets 28jul2020"; *Run a proc compare -MY control dataset vs. student dataset; Title "Comparing Group&i dataset with Charu's control dataset"; proc compare base=A1.final_tourism_charu_shankar compare=mbanA1.firstname_lastname_final; run; */ /* SOLUTION */ data _null_; set SASHELP.VTABLE (where=(libname='MBANA1')); call execute('proc compare base=A1.final_tourism_charu_shankar compare=MBANA1.'||memname||';run;'); run;Hope this helps.
Hello,
I am trying to run STOP command for each iteration. Below is the code. I am &load macro and trying to STOP it. Could someone please help. I am very new to SAS. Could you please help!
Thanks in advance!!!
proc sql noprint; select UniqueIdentifier into :load from allpids; %let load=&load; quit; data _null_; set allpids; CALL EXECUTE ('STOP spawned server id = &load" ); run;Hi Sourav, it is unclear what you are trying to achieve and why you need CALL EXECUTE. The STOP statement stops only current DATA STEP, not some "spawned server". I suggest you provide a bigger picture for your problem, describe it in a greater detail and submit it to the SAS Communities website - https://communities.sas.com/.
Hi Leonid,
I have used this approach many times in my work. But now, I have a data set that has 320k rows, and the building of statements has been running for over 12 hours with no idea how much longer to go. Is there a method to make each statement execute one at a time as it goes instead of waiting to build 320k statements? It would be nice to see progress as it goes by checking the records output to a table by the macro.
Thank you,
Robert
Hi Robert, Could you please paste here your data step code with a brief explanation of what you are trying to do? What code do you need to execute with each data step iteration?
Hi Leonid,
My datastep is calling a macro to get information from an Oracle database. However, it builds the 320k statements first and puts them in a queue before executing the first one. I would like it to execute the macro after each statement is built instead of waiting for all 320k to be built and queued first. Thank you.
/* Get GPS Data by ID and DOS */ data _NULL_; set data.ph_medicare_members_in_scope; str = catt('%get_gpsdatabyid(id=''', GPSID, ''', dos=''', PUT(DATEPART(DOS),yymmdd10.), ''');'); call execute (str); run;Hi Robert,
You can do it with the following approach:
%macro loop; /* Get number of observations */ data _null_; call symputx('N',n); stop; set data.ph_medicare_members_in_scope nobs=n; run; /* Get GPS Data by ID and DOS */ %do i=1 %to &N; data _NULL_; p = &i; set data.ph_medicare_members_in_scope point=p; str = catt('%get_gpsdatabyid(id=''', GPSID, ''', dos=''', PUT(DATEPART(DOS),yymmdd10.), ''');'); call execute (str); run; %end; %mend loop; %loop;The key here is to execute CALL EXECUTE with your macro call in separate data steps. So I added macro loop and point= option to read new observation. Does that help?
Thank you. That is a cool approach. I changed it to a tmp dataset with only 10 records, but I get the below error on each loop. It seems to be in relation to the loop variable, but I'm not sure what is wrong.
SYMBOLGEN: Macro variable N resolves to 10 MLOGIC(LOOP): %DO loop beginning; index variable I; start value is 1; stop value is 10; by value is 1. MPRINT(LOOP): data _NULL_; SYMBOLGEN: Macro variable I resolves to 1 NOTE: Line generated by the macro variable "I". 24 1 _ 22 200 MPRINT(LOOP): set tmp point=1; MPRINT(LOOP): str = catt('%get_gpsdatabyid(id=''', GPSID, ''', dos=''', PUT(DATEPART(DOS),yymmdd10.), ''');'); MPRINT(LOOP): call execute (str); MPRINT(LOOP): run; ERROR 22-322: Expecting a name. ERROR 200-322: The symbol is not recognized and will be ignored.Hi Robert,
That is because point= option expects variable name, not a value. Here I have corrected it for you. Please replace
with
and let me know how it works.
Without the put statements in XYZ1 and XYZ2 macros, below is what the log shows.
Before calling macro = 1
After calling macro = 1
Before calling macro = 2
After calling macro = 2
Before calling macro = 3
After calling macro = 3
1 + %xyz1(1);
ERROR: Execution terminated by an %ABORT statement.
What I expect is,
Before calling macro = 1
After calling macro = 1
Before calling macro = 2
After calling macro = 2
Before calling macro = 3
After calling macro = 3
1 + %xyz1(1);
ERROR: Execution terminated by an %ABORT statement.
2 + %xyz1(2);
ERROR: Execution terminated by an %ABORT statement.
3 + %xyz1(3);
ERROR: Execution terminated by an %ABORT statement.
Hi Ramesh,
The blog format is not intended for helping with code debugging. However, I can point out several inconsistencies in your code that might help you.
1. When debugging macros you should not use options nomlogic nomprint nosymbolgen nonotes; - this is not helpful. Quite contrary, you should use options mlogic mprint symbolgen notes;
2. When you use data step as a driver for your CALL EXECUTE, it does not make sense to use macro DRIVER. Get rid of %Macro Driver(); and %Mend; %Driver.
3. What is a purpose of using set sashelp.class if you don't use any of its variables in CALL EXECUTE? You use strip(4), why?
4. Finally, your code iterates correctly through the data _null_ step since you have if s_no = 2 then stop;
Based on the limited information about your goal, my guess is that instead of the whole macro %Driver you just need the following:
data _null_; retain s_no; set sashelp.class end=eof; if _n_ = 1 then s_no = 0; s_no = s_no + 1; put 'Before calling macro = ' s_no; call execute('%nrstr(%xyz1('||strip(s_no)||'));'); put 'After calling macro = ' s_no; if s_no = 3 then stop; run;Thanks Leonid!
The 2nd point really helped to achieve what I wanted to do.
All your other pointers are making sense, and the above shared code is just an example. My original code, considered all these points.
Thanks again!
Ramesh
Great, I am glad I was able to help.
Hi Leonid,
I'm running into a problem with running macros (with %abort statement in one of the sub-macros) in loop using CALL EXECUTE function in batch mode.
I've posted the question in SAS Communities, and pasting the link here to see if can get some guidance from you. Thanks for your time as always!
https://communities.sas.com/t5/SAS-Programming/SAS-help-Batch-Mode-Running-macros-in-loop-with-abort-statement/m-p/668419#M200360
Thanks,
Ramesh
Hi Ramesh, I am not sure why you use %abort statement. Why not use %return statement which causes normal termination of the currently executing macro? Try it out and let me know if it works for you.
Hello Leonid,
Thanks for your response!
The reason I'm using %abort instead of %return statement is because I don't want the subsequent macro calls within the Main_Macro to execute once the QuitOnError macro is called.
Please see the below example where the Main_Macro has n-number of macros, and once the QuitOnError macro is executed from any of the macros, no subsequent statements or macros should execute. In this case I have coded, both %return and %abort statement conditionally to show the execution difference.
OPTIONS NOMPRINT NOMLOGIC NOSYMBOLGEN NONOTES; %Macro QuitOnError(abortOrReturn); %put &abortOrReturn. in QuitOnError macro; %if &abortOrReturn = abort %then %abort; %else %if &abortOrReturn = return %then %return; %Mend; %Macro macro_n(iteration); %put &iteration. - Macron successful; PROC SQL; SELECT * FROM SASHELP.CLASS; QUIT; %put &iteration. - Class Table count = &sqlobs.; %Mend; %Macro Macro_4(iteration); %put &iteration. - Macro4 successful; PROC SQL; SELECT * FROM SASHELP.CLASS; QUIT; %put &iteration. - Class Table count = &sqlobs.; %Mend; %Macro Macro_3(iteration); %put &iteration. - Macro3 Errored; %put &iteration. - calling QuitOnError from Macro3; %if &iteration = 2 %then %QuitOnError(abort); %else %QuitOnError(return); PROC SQL; SELECT * FROM SASHELP.CLASS; QUIT; %put &iteration. - Class Table count = &sqlobs.; %Mend; %Macro Macro_2(iteration); %put &iteration. - Macro2 started; PROC SQL; SELECT * FROM SASHELP.CLASS; QUIT; %put &iteration. - Class Table count = &sqlobs.; %Mend; %Macro Macro_1(iteration); %put &iteration. - Macro1 successful; PROC SQL; SELECT * FROM SASHELP.CLASS; QUIT; %put &iteration. - Class Table count = &sqlobs.; %Mend; %Macro Main_Macro(iteration); %Macro_1(&iteration); %Macro_2(&iteration); %Macro_3(&iteration); %Macro_4(&iteration); %Macro_n(&iteration); %Mend; DATA _NULL_; DO i = 1 TO 3; CALL EXECUTE('%NRSTR(%Main_Macro('||STRIP(i)||'))'); END; RUN;Put statement Log outputs of the above code:
1 + %Main_Macro(1)
1 - Macro1 successful
1 - Class Table count = 19
1 - Macro2 started
1 - Class Table count = 19
1 - Macro3 Errored
1 - calling QuitOnError from Macro3
return in QuitOnError macro
1 - Class Table count = 19
1 - Macro4 successful
1 - Class Table count = 19
1 - Macron successful
1 - Class Table count = 19
---------------------------------------------------------------
2 + %Main_Macro(2)
2 - Macro1 successful
2 - Class Table count = 19
2 - Macro2 started
2 - Class Table count = 19
2 - Macro3 Errored
2 - calling QuitOnError from Macro3
abort in QuitOnError macro
ERROR: Execution terminated by an %ABORT statement.
----------------------------------------------------------------
3 + %Main_Macro(3)
3 - Macro1 successful
3 - Class Table count = 19
3 - Macro2 started
3 - Class Table count = 19
3 - Macro3 Errored
3 - calling QuitOnError from Macro3
return in QuitOnError macro
3 - Class Table count = 19
3 - Macro4 successful
3 - Class Table count = 19
3 - Macron successful
3 - Class Table count = 19
-----------------------------------------------------------
Based on the log, I'm expecting the 2nd iteration behavior in my process, so I'm using %abort statement and not %return statement.
Though the 2nd iteration is aborted, it is running the 3rd iteration in interactive mode.
But in the batch mode, the process works fine until 2nd iteration. 3rd iteration starts fine, but all the observations are 0. I think it is because the OBS option is set 0 when %abort statement is executed in batch mode.
Below is log of the same code from batch mode:
1 + %Main_Macro(1)
1 - Macro1 successful
1 - Class Table count = 19
1 - Macro2 started
1 - Class Table count = 19
1 - Macro3 Errored
1 - calling QuitOnError from Macro3
return in QuitOnError macro
1 - Class Table count = 19
1 - Macro4 successful
1 - Class Table count = 19
1 - Macron successful
1 - Class Table count = 19
-----------------------------------------------------------
2 + %Main_Macro(2)
2 - Macro1 successful
2 - Class Table count = 19
2 - Macro2 started
2 - Class Table count = 19
2 - Macro3 Errored
2 - calling QuitOnError from Macro3
abort in QuitOnError macro
ERROR: Execution terminated by an %ABORT statement.
-----------------------------------------------------------
3 + %Main_Macro(3)
3 - Macro1 successful
3 - Class Table count = 0
3 - Macro2 started
3 - Class Table count = 0
3 - Macro3 Errored
3 - calling QuitOnError from Macro3
return in QuitOnError macro
3 - Class Table count = 0
3 - Macro4 successful
3 - Class Table count = 0
3 - Macron successful
3 - Class Table count = 0
-----------------------------------------------------------
Please note that the Main_Macro was written some back this way to use %abort statement. Now I’m just trying to run it in loop without not making much changes to code. There might be some elegant ways to check for errors at the start of each macro and go to the end if there were any errors.
Now I’m trying to see if there is a possibility at all to achieve the need without making the big change as there close to couple hundred macros within the Main_Macro.
Thanks again for reading this detailed note, and taking sometime to think about solution.
Ramesh
Hi Ramesh,
I seems that the deeper you get the more efforts you need to dig yourself out, figuratively speaking. I think it's about time to change the direction. In your description, you never mention the nature of your "error" that requires termination of your macro(s). If that error is dependent on your data, why not clean the data first, before you even call those macros. In any case, can you try excluding your error-causing situation earlier in the process. Otherwise, you will have to "pay the price" of catching it too late.
Hi There,
I'm trying to run a set of macros(some are nested) in loop even when one/all of the occurrence fails/aborted conditionally.
So I decided to use CALL EXECUTE with %NRSTR option as below.
In the below code the main macro is XYZ1 which calls XYZ2. The XYZ2 macro is conditionally aborted. My need is to continue running the next occurance of XYZ1 even when the XYZ2 fails/aborts. But for some reason, the entire run stops when the code encounters %abort statement in XYZ2. I also tried %abort cancel in place of %abort in XYZ2.
I'm running the below program using SASEG.
%Macro XYZ1(mi_stopCount); %local i j k; %do i = 1 %to 5; %put XYZ1 &i; %XYZ2(&mi_stopCount.); %put -------------; %end; %Mend; %Macro XYZ2(mi_stopCount); %local i j k; %do i = 1 %to 5; %put XYZ2 &i; %if &i = &mi_stopCount. %then %do; %put XYZ2 Aborting at &i.; %abort; %end; %end; %Mend; %Macro Driver(); options nomlogic nomprint nosymbolgen nonotes; %local i j k; data _null_; retain s_no; set sashelp.class end=eof; if _n_ = 1 then s_no = 0; s_no = s_no + 1; put 'Before calling macro = ' s_no; call execute('%nrstr(%xyz1('||strip(4)||'));'); put 'After calling macro = ' s_no; if s_no = 2 then stop; run; %Mend; %DriverNote: I was able to do this earlier with the same approach but I'm missing something now.
Any help would be greatly appreciated.
Thanks,
Ramesh
HI Leonid,
I need one help in using call execute.
data _null_;
set one;
call symput ('var1',&var2.);
CALL EXECUTE('%test1(&var1.)');
run;
%macro test1(var1);
libname dm 'some path';
some code;
%mend;
My question in above code is, whenever I execute this code. Call Execute calls the macro for each iteration but when it goes inside the macro test1, it always executes libname at last. Not in sequence. I am wondering why it's happening. I mean to say libname gets executed once the macro test1 gets completed. I could see the log in that way.
Please help.
Hi Ravi,
Here is a couple pieces of advice:
1) Move your macro definition before the data step in which you call that macro - it must be known to the SAS compiler before you call it.
2) You don't need call symput in your data step, since macro variable var1 is only available outside of this data step (I don't know why you create macro variable var1 out of macro variable var2, more likely you want to use a variable value - not macro variable - from your dataset one as a parameter value that you pass to your macro).
3) When you call your macro enclose it into %nrstr() macro function to deter its execution until after the data step (see explanation in this blog post above).
4) Use option symbolgen, mlogic and mprint for macro debugging.
5) With that said, your code should look something like this:
Hope this helps.
the data step shorter version call execute also worked! I am posting the complete code here: thanks again Leonid!
%MACRO SCANLOOP(in_data); data _null_; set &in_data; call execute('%nrstr(%getData('||email||','||zip_to||','||case_id||','||objectid||'))'); if strip(route) ne 'NA' then call execute('%nrstr(%filter_route_sql('||route||'))'); call execute('%nrstr(%export_excel('||objectid||'))'); call execute('%nrstr(%send_email('||email||','||zip_to||','||case_id||','||objectid||','||route||'))'); run; %MEND SCANLOOP; %SCANLOOP(valid_list);Great! Thank you, Amy, for sharing your entire code. Now you have officially conquered CALL EXECUTE.
Yes! it ran successfully - optionally invoked the filter macro to filter my data if the route id is not NA! I'll follow up to try the second approach - use the call execute in a data step and use the data step implicit loop for variables. I'll report back. thanks so much!
Hi Leonid, first, thank you so much for your response! Yes, you correctly pointed out where my errors are! I don't need the second data step for the call execute. I am following your suggestion on the first response, the invocation of the macro %filter_route(&route_in) is working fine, but I am having a secondary issues of the macro string comparison, ( for some darn reason, the passed in route macro var refuses to equal to "NA" , even it is 'NA' from valid_list dataset! ). that's why I am delaying my declaration of success:-).
after I successfully run the code, then I will experiment with your second suggestion, I like that shorter code better, I tried to run it but it doesn't run as it is, it has syntax issues. what are those !! do as in '!!email!!'? stripping the blank spaces?
again, thanks a lot and I am much closer to get it right!
Hi Amy,
I am glad to hear you are getting closer to a solution. For "NA" vs. 'NA' you can try removing double quotes from %if condition as in the following:
In my second example !! are concatenation operator, and yes, I forgot to strip the blank spaces. Instead of
call execute('%nrstr(%send_email('!!email!!','!!zip_to!!','!!case_id!!','!!objectid!!','route!!'))');you should have something like this (catx function removes leading and trailing blanks, inserts delimiters, in this case comma, and returns a concatenated character string):
str='%nrstr(%send_email(' !! catx(',',email,zip_to,case_id,objectid,route) !!'))'; call execute(str);See an example in my other blog post: https://blogs.sas.com/content/sgf/2018/07/17/delete-sas-logs-admin/ - at the very end; only I use there named macro parameters, you use positional.
Good luck.
I want to use the conditional call execute inside a outer macro that passes in a variable as the input of the inner macro, I tried this code, it's not working, says the statement is not valid or used out of order.. appreciate any help!
%MACRO SCANLOOP(in_data,FIELD1,FIELD2,FIELD3,FIELD4,FIELD5);
/* First obtain the number of */
/* records in DATALOG */
DATA _NULL_;
IF 0 THEN SET &in_data NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;
/* loop from one to number of */
/* records */
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET &in_data (FIRSTOBS=&I);
CALL SYMPUT('email_in',&FIELD1);
CALL SYMPUT('zip',&FIELD2);
CALL SYMPUT('caseid_in',&FIELD3);
CALL SYMPUT('objectid_in',&FIELD4);
CALL SYMPUT('route_in',&FIELD5);
STOP;
RUN;
data _null_;
%getData(&email_in,&zip,&caseid_in,&objectid_in);
%if "&route_in" ne "NA" %then
call execute('%nrstr(%filter_route(&route_in));');
/*%filter_route(&route_in);*/
%export_excel(&objectid_in);
%send_email(&email_in,&zip,&caseid_in,&objectid_in,&route_in);
run;
%END;
%MEND SCANLOOP;
/* Call SCANLOOP */
%SCANLOOP(valid_list,email,zip_to,case_id, objectid,route);
Hi Amy,
I don't have the whole picture since you have not provided the component macros %getData, %export_excel, %send_email, and %filter_route. You invoke these macros within a data step, and I suspect they do not generate data step statements. That is why you get "statement is not valid or used out of order".
I think, you don't need the last data _null_ step and call execute for your purpose. Instead of the last data _null_ step, try this:
Try it and let me know if it works for you.
Amy, I believe you are mixing two approaches - 1) macro loops and 2) data step loops. Using call execute can substantially shrink your SAS code; your macro will not have a %do-loop, will have a single data _null_ step, and would look something like this:
%MACRO SCANLOOP(in_data); data _null_; set &in_data; if route ne 'NA' then call execute('%nrstr(%filter_route('!!route!!'))'); call execute('%nrstr(%export_excel('!!objectid!!'))'); call execute('%nrstr(%send_email('!!email!!','!!zip_to!!','!!case_id!!','!!objectid!!','route!!'))'); run; %MEND SCANLOOP; /* Call SCANLOOP */ %SCANLOOP(valid_list);Hi Leonid,
Your post is nicely done!
When I create data-driven code, I too create a "control" table that helps drive the processing. I'm also a big fan of the family of CAT functions and like the way you specified the CATS function in your data-driven code.
Keep up the good work!
Kirk
Thank you, Kirk! Your comment is much appreciated.
Hello Leonid, and thank you for covering my favorite toy in SAS! Look at this beauty, for example:
filename dir pipe "dir /b &_data_raw.*.csv";
data _null_;
length filename $256;
infile dir length= reclen;
input filename varying256. reclen;
call execute( cat( "PROC IMPORT DATAFILE= ""&_data_raw.", strip( filename ), """",
" OUT= CRF.", scan( filename, 1, "." ),
" DBMS= CSV REPLACE; GUESSINGROWS=3000; RUN;"
)
);
run;
The main obstacle to writing any generated code is, in my opinion, having a clear mental model of the code processing and understanding what data are available when. For example, if one puts CALL EXECUTE inside a macro, the macro is finished by the time of CALL EXECUTE() run so any additional SAS code generated by the macro will be read by SAS before any EXECUTE's code, so one cannot issue any finalization commands inside the macro, for example. You have another great example about SYMPUT'ting things too early for the macros to pick up.
To help switching the mental focus, I typically write the generated code in ALL CAPS while the normal open code in lower case.
BY groups help write more involved CALL EXECUTE code. For example, imagine you were provided a set of data with some silly suffixes attached to the variable names, e.g.
FOO_111
BAR_222
while really all you need is FOO and BAR (true story). Now fun begins:
data _null_;
set sashelp.vcolumn;
where libname = "CRF" and memtype= "DATA";
by memname;
if first.memname then
call execute( cat( "DATA CRF.", left( memname ), ";",
" SET CRF.", left( memname ), "; ",
"RENAME "
)
);
call execute( cat( name, " = ", scan( name, 1, "_" ), " " ) );
if last.memname then
call execute( "; RUN; " );
run;
so for each line in the dataset, it is just another BEFORE=AFTER pair for the normal RENAME operator what is preceded by DATA STEP opening for each new dataset and appended by semicolon and RUN at the dataset end.
Another often-overlooked area to use CALL EXECUTE is COMPUTE blocks in PROC REPORT what might be worth a post on its own.
Thank you, Anton, for being an inspiration and for extending CALL EXECUTE usage scenarios. Great additions which I am sure our readers will appreciate.
Nice article!
Sometimes I use this technique:
/* _debug is a global macro variable */
%if &_debug %then
%do;
filename _code "%sysfunc(pathname(savecode))/savedcode.sas";
%end;
%else %do;
filename _code CATALOG "work.mycat.tmpsource.source";
%end;
data _null_;
set mydata;
file _code;
... put statements ...
run;
%include _code;
filename _code clear;
It is easier to review the generated code when things go wrong.
Lex
Thank you, Lex, for sharing your alternative approach. Could you please clarify why you store your generated SAS code in a file vs. catalog depending on a _debug mode (1/0)? Also, did you mean WORK when you placed savecode as an argument to the pathname() function?
It's for convenience. Sometimes you may want to review the code being generated. 'savecode' would be a library where I want this code to be saved. I could have used a complete or relative folder name, for example: filename _code "../code/savedcode.sas";
Of course a relative path assumes that SAS starts in a specific folder.
Thank you, Lex, for the clarification. I got it, you want to save your generated code in a location available to you outside of SAS as the WORK library will be wiped out when SAS session ends. But I am still not clear on storing your generated code in SAS catalog vs. file. Why do you prefer one way over another depending on debugging mode?
When I want to debug, I want to see the code outside of SAS. When I don't care about debugging, I store my code in a catalog, or I could have stored the code in a file in the WORK library. There is no strong preference here between file and catalog.
Thank you again. As I understand it, file or catalog is not essential, what is important whether you store it in a permanent location or in a temporary location (e.g. WORK library) that gets wiped out at the end of SAS session.