topic Re: PROC COMPARE in call execute in data step in SAS Programming

PROC COMPARE in call execute in data step

i_Van — Wed, 20 Nov 2019 13:03:29 GMT

Hello,

I have to compare 2 tables inside the macros and then run this macro with a set of parameters, but calling PROC COMPARE in different ways (direct call on two tables, calling macro via %MACRO_NAME, and calling macro from data step via call execute) gives different results

data t1;
input a $ b;
datalines;
a 1
b 2
c 3
run;

data t2;
input a $ b;
datalines;
a 1
b 2
c 3
d 4
run;

proc compare base=t1
     compare=t2 noprint;
run;
%put WARNING: direct compare &=sysinfo;

%macro compar(table1, table2, comment='');
	proc compare base=&table1
	     compare=&table2 noprint;
	run;
	%put WARNING: &comment &=sysinfo;
%mend;

%compar(t1,t2,comment=direct macro invoke);

data compare_table;
input a $ b $;
datalines;
t1 t2
run;

data null;
set compare_table;
call execute('%compar(' || a||','||b||',comment=macro invoke from data step)');
call execute('%compar(t1,t2,comment=macro invoke from data step full copy)');
run;

The direct call on two tables, calling macro via %MACRO_NAME obviously shows that tables are different

BUT calling macro from data step via call execute shows that tables are equal, even CALL EXECUTE('%MACRO') shows the wrong result.

Some remarks if any way around exists:

I have to compare 2 tables in macro to be able to use %if %then to choose the behavior of the program depending on the equity of tables

I have to call macros several times with the parameters storing in the table

Re: PROC COMPARE in call execute in data step

gamotte — Wed, 20 Nov 2019 13:19:16 GMT

Hello,

I think you have to use nrstr :

call execute('%nrstr(%compar)(' || a||','||b||',comment=macro invoke from data step)');call execute('%compar(t1,t2,comment=macro invoke from data step full copy)');

in order to avoid an early macro resolution. In your example, the macrovariable sysinfo is resolved before the execution of proc compare.

Re: PROC COMPARE in call execute in data step

Quentin — Wed, 20 Nov 2019 13:27:52 GMT

This is a clasic CALL EXECUTE timing problem.

When CALL EXECUTE executes a macro, it resolves all of the macro variable references and executes macro language statements immediately. It resolves them BEFORE any of the SAS code has actually executed. If you check your log, the %PUT statements show up before the PROC COMPARE step:

60   data null;
61   set compare_table;
62   call execute('%compar(' || a||','||b||',comment=macro invoke from data step)');
63   call execute('%compar(t1,t2,comment=macro invoke from data step full copy)');
64   run;

WARNING: macro invoke from data step SYSINFO=0
WARNING: macro invoke from data step full copy SYSINFO=0
NOTE: There were 1 observations read from the data set WORK.COMPARE_TABLE.
NOTE: The data set WORK.NULL has 1 observations and 2 variables.

NOTE: CALL EXECUTE generated line.
1   + proc compare base=t1        compare=t2 noprint;   run;

NOTE: There were 3 observations read from the data set WORK.T1.
NOTE: There were 4 observations read from the data set WORK.T2.

2   + proc compare base=t1        compare=t2 noprint;   run;

NOTE: There were 3 observations read from the data set WORK.T1.
NOTE: There were 4 observations read from the data set WORK.T2.

So your macro reference to &sysinfo is resolved before the PROC COMPARE has run. Which is not what you want.

To prevent this timing problem, you can add a macro quoting function, %NRSTR() to the call execute. This prevents CALL EXECUTE from actually executing the macro. Instead, call execute will generate the macro call, and put the macro call on the input stack.

Then after the data _null_ step has completed, each macro call will be executed, and the the timing works out because the PROC COMPARE can be run before the %PUT statement.

So changing the code to:

data null;
set compare_table;
call execute('%nrstr(%compar)(' || a||','||b||',comment=macro invoke from data step)');
call execute('%nrstr(%compar)(t1,t2,comment=macro invoke from data step full copy)');
run;

Gives a log:

65   data null;
66   set compare_table;
67   call execute('%nrstr(%compar)(' || a||','||b||',comment=macro invoke from data step)');
68   call execute('%nrstr(%compar)(t1,t2,comment=macro invoke from data step full copy)');
69   run;

NOTE: There were 1 observations read from the data set WORK.COMPARE_TABLE.
NOTE: The data set WORK.NULL has 1 observations and 2 variables.

NOTE: CALL EXECUTE generated line.
1   + %compar(t1      ,t2      ,comment=macro invoke from data step)

NOTE: There were 3 observations read from the data set WORK.T1.
NOTE: There were 4 observations read from the data set WORK.T2.

WARNING: macro invoke from data step SYSINFO=128
2   + %compar(t1,t2,comment=macro invoke from data step full copy)

NOTE: There were 3 observations read from the data set WORK.T1.
NOTE: There were 4 observations read from the data set WORK.T2.

WARNING: macro invoke from data step full copy SYSINFO=128

While adding the %NRSTR() to the code makes it a tiny bit harder to read, it makes your log much cleaner, because you can see the only lines in the log generated by the call execute (with the + prefix) are the macro calls themselves, not the SAS code from executing the macro.

Adding %NRSTR() allows call execute to generate macro calls and place them on the input stack for execution, rather than execute the macro while the DATA _NULL_ step is running.

Re: PROC COMPARE in call execute in data step

i_Van — Wed, 20 Nov 2019 13:43:10 GMT

Thank you greatly