problem call execute

mrom34 — Wed, 29 Sep 2010 22:07:22 GMT

Hi,

I have a problem with call execute that I don’t understand. Basically, what I am doing is merging 2 datasets (old and new versions) and then, if a variable has been updated, I write the information in an audit table. Below is a simplified version of my code.

In my dataset test, I have only one row with var1_old="oldval" and var1_new="new."
The macro createdataaudit should output one record in the dataset audit. However, the row is not added to the table.
I found the reason but I don’t understand it. If I remove the "dot” from the variable var1_new, the macro is executed and one row is added to the dataset. It doesn’t work with the ‘dot’ at the end of the variable var1_new.
Also if I had some text after the “dot”, the macro executes and writes a row in audit table.

If someone could explain to me, I would really appreciate. Thanks a lot!!!!

%macro createdataaudit(outdata,newval,oldval);

%if &newval ne &oldval %then %do;
%if %sysfunc(exist(&outdata)) %then %do ;
PROC SQL;
INSERT INTO &outdata
SET newval=&newval,oldval=&oldval ;
QUIT;
%end;
%end;

%mend createdataaudit;

data test;
set test;
if strip(var1_old) ne strip(var1_new) then do;
call execute('%createdataaudit(audit,'||quote(strip(var1_old))||','||quote(strip(var1_new))||');');
end;
run;

Re: problem call execute

art297 — Thu, 30 Sep 2010 00:39:16 GMT

Mrom,

Quite possibly I'm not following everything that you're doing, but WHY would you expect the variable TO work with a period after it?

A period would only be relevant after a variable name if you were referring to a macro variable.

Art

Re: problem call execute

Peter_C — Thu, 30 Sep 2010 07:04:26 GMT

At what stage do you expect the macro invoked by the call execute() to:
1 compile
2 resolve
??

Re: problem call execute

polingjw — Thu, 30 Sep 2010 15:12:15 GMT

If you take a longer look at the original posting you will see that “new.” is not the name of a variable, just plain text. After I tried to run this program, I got a little confused myself.

First of all, I don’t think that call execute has anything to do with the problem. The record is not appended to the audit dataset if you invoke the macro like %createdataaudit(audit,"old val","new."), which is the text generated by the call execute statement. See the log below:

[pre]
1159 data audit;
1160 length newval oldval $ 8;
1161 run;

NOTE: Variable newval is uninitialized.
NOTE: Variable oldval is uninitialized.
NOTE: The data set WORK.AUDIT has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

1162
1163 %macro createdataaudit(outdata,newval,oldval);
1164
1165 %if &newval ne &oldval %then %do;
1166 %if %sysfunc(exist(&outdata)) %then %do ;
1167 PROC SQL;
1168 INSERT INTO &outdata
1169 SET newval=&newval, oldval=&oldval;
1170 QUIT;
1171 %end;
1172 %end;
1173
1174 %mend createdataaudit;
1175
1176 options mprint mlogic;
1177 %createdataaudit(audit,"old val","new.")
MLOGIC(CREATEDATAAUDIT): Beginning execution.
MLOGIC(CREATEDATAAUDIT): Parameter OUTDATA has value audit
MLOGIC(CREATEDATAAUDIT): Parameter NEWVAL has value "old val"
MLOGIC(CREATEDATAAUDIT): Parameter OLDVAL has value "new."
MLOGIC(CREATEDATAAUDIT): %IF condition &newval ne &oldval is TRUE
MLOGIC(CREATEDATAAUDIT): %IF condition %sysfunc(exist(&outdata)) is TRUE
MLOGIC(CREATEDATAAUDIT): Ending execution.
[/pre]

Here is where I’m getting confused, if the log says that the condition sysfunc(exist(&outdata)) is true, then why is the SQL statement not written to the input stack? If you now invoke the macro without the period, here is the result:

[pre]
1178 %createdataaudit(audit,"old val","new")
MLOGIC(CREATEDATAAUDIT): Beginning execution.
MLOGIC(CREATEDATAAUDIT): Parameter OUTDATA has value audit
MLOGIC(CREATEDATAAUDIT): Parameter NEWVAL has value "old val"
MLOGIC(CREATEDATAAUDIT): Parameter OLDVAL has value "new"
MLOGIC(CREATEDATAAUDIT): %IF condition &newval ne &oldval is TRUE
MLOGIC(CREATEDATAAUDIT): %IF condition %sysfunc(exist(&outdata)) is TRUE
MPRINT(CREATEDATAAUDIT): PROC SQL;
MPRINT(CREATEDATAAUDIT): INSERT INTO audit SET newval="old val", oldval="new";
NOTE: 1 row was inserted into WORK.AUDIT.

MPRINT(CREATEDATAAUDIT): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MLOGIC(CREATEDATAAUDIT): Ending execution.
[/pre]

Now, the SQL statement is correctly written to the input stack. The problem must have something to do with the way that the macro processor interprets the period.

Just to make things more confusing, I said earlier that I didn’t think call execute had anything to do with the problem. However, take a look at the log when you do invoke the macro with call execute:

[pre]
1179 data test;
1180 var1_old = 'old val';
1181 var1_new = 'new.';
1182 run;

NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

1183
1184 data test;
1185 set test;
1186 if strip(var1_old) ne strip(var1_new) then do;
1187 call
1187! execute('%createdataaudit(audit,'||quote(strip(var1_old))||','||quote(strip(var1_new))||');
1187! ');
1188 end;
1189 run;

NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

MLOGIC(CREATEDATAAUDIT): Beginning execution.
MLOGIC(CREATEDATAAUDIT): Parameter OUTDATA has value audit
MLOGIC(CREATEDATAAUDIT): Parameter NEWVAL has value "old val"
MLOGIC(CREATEDATAAUDIT): Parameter OLDVAL has value "new."
MLOGIC(CREATEDATAAUDIT): %IF condition &newval ne &oldval is TRUE
MLOGIC(CREATEDATAAUDIT): %IF condition %sysfunc(exist(&outdata)) is TRUE
MPRINT(CREATEDATAAUDIT): PROC SQL;
MPRINT(CREATEDATAAUDIT): INSERT INTO audit SET newval="old val", oldval="new.";
MPRINT(CREATEDATAAUDIT): QUIT;
MLOGIC(CREATEDATAAUDIT): Ending execution.
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

NOTE: CALL EXECUTE generated line.
1 + PROC SQL; INSERT INTO audit SET newval="old val", oldval="new."; QUIT;;
[/pre]

Note that now, the SQL statement is displayed on the mprint lines and the log says that call execute did generate the SQL statement. However, it’s obvious that the statement was never executed. If the statement were executed, there would have been a note in the log pertaining to process time used by the SQL procedure.

Not that it helps explain why the previous code did not work, but by making some simple modifications to the macro and the way that it’s called, you can create a macro that will successfully append the record to the dataset:

[pre]

1190 %macro createdataaudit(outdata,newval,oldval);
1191
1192 %if &newval ne &oldval %then %do;
1193 %if %sysfunc(exist(&outdata)) %then %do ;
1194 PROC SQL;
1195 INSERT INTO &outdata
1196 SET newval="%bquote(&newval)", oldval="%bquote(&oldval)";
1197 QUIT;
1198 %end;
1199 %end;
1200
1201 %mend createdataaudit;
1202
1203 data test;
1204 set test;
1205 if strip(var1_old) ne strip(var1_new) then do;
1206 call execute('%createdataaudit(audit,'||strip(var1_old)||','||strip(var1_new)||');');
1207 end;
1208 run;

NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

MLOGIC(CREATEDATAAUDIT): Beginning execution.
MLOGIC(CREATEDATAAUDIT): Parameter OUTDATA has value audit
MLOGIC(CREATEDATAAUDIT): Parameter NEWVAL has value old val
MLOGIC(CREATEDATAAUDIT): Parameter OLDVAL has value new.
MLOGIC(CREATEDATAAUDIT): %IF condition &newval ne &oldval is TRUE
MLOGIC(CREATEDATAAUDIT): %IF condition %sysfunc(exist(&outdata)) is TRUE
MPRINT(CREATEDATAAUDIT): PROC SQL;
MPRINT(CREATEDATAAUDIT): INSERT INTO audit SET newval="old val", oldval="new.";
MPRINT(CREATEDATAAUDIT): QUIT;
MLOGIC(CREATEDATAAUDIT): Ending execution.
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

NOTE: CALL EXECUTE generated line.
1 + PROC SQL;
1 + INSERT INTO audit SET newval="old val", oldval="new.";
NOTE: 1 row was inserted into WORK.AUDIT.

1 + QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
[/pre]

Even though the above macro does work, I would really like to understand exactly why the original macro failed.

Re: problem call execute

mrom34 — Thu, 30 Sep 2010 16:40:46 GMT

Hi,

Thanks a lot for your answer. You macro works very well.

I found another solution. In the macro createaudit, if I replace %if &newval ne &oldval %then %do; by if %str(&newval) ne %str(&oldval) %then %do;, then the macro appends the record to the dataset.

I am still confused about this program and I would really like to understand why it is not working.

Romain

topic Re: problem call execute in SAS Programming

problem call execute

Re: problem call execute

Re: problem call execute

Re: problem call execute

Re: problem call execute