SAS - Call execute
Sophia Terry 
I have used call execute to call a macro to write the output file line by line but it has written only the last line in the output file.
%Macro INPS(ipdsn); filea = &ipdsn; %put MACRO: ipdsn -->: &ipdsn; %put MACRO: ipdsn -->: filea; newHostDSN = prxchange (re_iPat, -1, trim(&filea)); do i=1 to f_count; set Var_Patterns(keep=Pat_Name Pat_Value) point=i nobs=f_count; re_vPat = cats('s/\$', Pat_Name, '(\.)?/', Pat_Value, '/i'); newHostDSN = prxchange (re_vPat, -1, trim(newHostDSN)); end; %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
MACRO: ipdsn -->: fileaI want to store the data step value (file name) in a variable inside macro(filea) and use it for other validation in macro (newhostdsn and do). any idea how can i do this?
52 Answers
It will execute immediately.
If an EXECUTE routine argument is a macro invocation or resolves to one, the macro executes immediately. Execution of SAS statements generated by the execution of the macro will be delayed until after a step boundary. SAS macro statements, including macro variable references, will execute immediately.
Using your example:
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;
%Macro INPS(ipdsn); %put MACRO: ipdsn -->: &ipdsn;
%Mend;Log Output:
DATA STEP: ipdsn -->: AAAAAAAAAAAAAA
MACRO: ipdsn -->: AAAAAAAAAAAAAA
DATA STEP: ipdsn -->: BBBBBBBBBBBBBB
MACRO: ipdsn -->: BBBBBBBBBBBBBBThe issue here is that you're not really understanding how SAS variables (and macro variables) work.
What CALL EXECUTE does is tells SAS to execute the code inside the call execute immediately after the data step completes. Your example code contains data step lines, but they're not inside a data step - they're just executed inside open code. Put them in a data step, and they'll execute fine.
%Macro INPS(ipdsn); data _null_; filea = "&ipdsn"; %put MACRO: ipdsn -->: &ipdsn; %put MACRO: ipdsn -->: filea; newHostDSN = trim(filea); put newHostDSN=; 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;That puts the right value inside newHostDSN.
Now, there is something else important: the macro statements do execute "before" the run. This is pretty important if your macro is more complicated.
See the following code.
%Macro namesbyage(name); proc sql; select age into :age from sashelp.class where name = "&name"; quit; data _null_; set sashelp.class; where age = &age.; put age= name=; run; %Mend;
/* do not run this at first:
%symdel age;
*/
data _null_; set sashelp.class; call execute('%namesbyage('||name||')');
run;Leaving the commented out part out, run it twice. The first time, it will work - except you'll get a whole bunch of warnings that &age does not exist - but it will correctly put various things to the log, age 12, age 13, et cetera.
The second time, it won't do the same thing! You won't get the warning about &age not existing - but every single one of the executions will be the same age, the last age from the earlier run.
That is because, the first time, it's not converting &age the symbol into anything - it leaves it as &age, since there's no value yet. That way, when the proc sql; select into creates &age, it works - because then it's resolved.
However, the second time, &age exists! That means that when it creates the call execute, it is able to resolve &age right then - no need to wait. So you get 15 (the present value of &age before anything runs) substituted in. That's not what you want!
Now uncomment %symdel age; . That deletes the age symbol, making every run like the first - it works, but with the warnings about &age not resolving.
How can you fix this? Well, one way is to use that %symdel statement. That deletes age, so you know for sure it won't be prematurely resolved. That's not really the best way, but it works.
Better, is to use %nrstr, which "hides" age from the parser.
%Macro namesbyage(name); proc sql; select age into :age from sashelp.class where name = "&name"; quit; data _null_; set sashelp.class; where age = %nrstr(&age); put age= name=; run;
%Mend;Now, age is not resolved until later on - after the macro has been called by the call execute.
Third, you can do the same thing inside the call execute!
data _null_; set sashelp.class; call execute('%nrstr(%namesbyage('||name||'))');
run;This would hide the whole macro from the call execute parser. This is easiest in the scheme of things - everything happens later, at the expected time.
You also could use something other than call execute to execute your macros - for example, you could use my select into syntax to create a macro variable to do all of the executions.
proc sql; select cats('%namesbyage(',name,')') into :namelist separated by ' ' from sashelp.class;
quit;
&namelist.;Here the namelist macro variable actually contains the text for all of the macro executions - so just running that macro variable in open code calls them all as if you'd typed them all out. This has advantages (simultaneous execution) and disadvantages (limited to 60k characters in particular). You could also write the calls out to a text file and %include that text file.
Why didn't your example fail? Because it was using a macro parameter that wasn't a macro variable, that's why. If ipdsn had existed as a separate macro variable, not a parameter, it would be resolved - and fail to work, which is most likely what you're seeing in your real example.
