CALL EXECUTE trouble with local vs. global scope (and how to loop)

[FK1]

Hi Folks,

here is a code snippet:

%macro distinct_values(in, col1, col2);
%GLOBAL in col1 col2 unique_values1 unique_values2 ;
  	proc sql noprint;
    select count(distinct &col1) into :unique_values1
    from &in ;

	select count(unique(&col2)) into :unique_values2
	from &in ;
	quit;

	%PUT DISTINCT VALUES OF COL1: &unique_values1. ;
	%PUT #######################################################;
	%PUT DISTINCT VALUES OF COL2: &unique_values2. ;
%mend;


%MACRO TEST;
data _test;

call execute("%distinct_values(in = SASHELP.DEMOGRAPHICS , col1=id, col2=iso);");

		    distnct_col1 	= &unique_values1.;
			distnct_col2 	= &unique_values2.;

run;
%MEND;
%TEST;

I get the error message:

ERROR: Attempt to %GLOBAL a name (IN) which exists in a local environment.
ERROR: Attempt to %GLOBAL a name (COL1) which exists in a local environment.
ERROR: Attempt to %GLOBAL a name (COL2) which exists in a local environment.
DISTINCT VALUES OF COL1:
#######################################################
DISTINCT VALUES OF COL2:
NOTE 137-205: Line generated by the invoked macro "TEST".
75          data _test;  call execute("%distinct_values(in = SASHELP.DEMOGRAPHICS , col1=id, col2=iso);");        distnct_col1  =
75       ! &unique_values1.;    distnct_col2  = &unique_values2.;  run;
                           _
                           22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, INPUT, PUT.  

So my questions would be:

1.) Why does the macro not work even though I clearly state that "%GLOBAL in col1 col2 unique_values1 unique_values2 ;" ?

2.) Imagine, I want to loop through evry dataset in the sashelp-library (and therefore need to change the colum parameters accordingly). What would be the best way to do this? (The difficulty I am having is, that in the line where I do the call execute I have macro variables as parameter inputs which are not being recognized, as everthing in the parentheses of the call execute is enclosed in quotation marks.

Any suggestions?

Thank's in advance,

FK1

[RW9]

The problem is scope.  You have put the %global inside the macro, you can't create global macro variables - i.e. ones available to all the program inside the scope of the macro.

As for what you want to do, provide some example.  That code you provide isn't the way to go.

data _null_;
  set sashelp.column (where=(libname="SASHELP"));
  /* The above gets all the columns from all the tables */
  call execute(...);
  /* the above can create code for each column of each table as it is done for each obs returned, so for example if I want get count (assuming total is already created as empty table */
  call execute('proc sql;
                      insert into TOTAL
                      set   TABLE_NAME="'||strip(memname)||'",
                            COL_NAME="'||strip(name)||'",
                            TOTAL=(select count(distinct('||strip(name)||')) from SASHELP.'||strip(memname)||');
                     quit;');
run;

The second call execute shows the proc sql which will be created for each observation returned by the where clause - it may take a while to run though.  Alternatively you can simply call a proc freq on each table with _numeric_ and _character_ for each table, then combine the results.

[Ksharp]

%macro distinct_values(in, col1, col2);

---> this means &in &col1 &col2 have already been LOCAL macro variable,
you can not use %GLOBAL to redefine them again.

[Kurt_Bremser]

in, col1 and col2 are macro parameters. They can NEVER be global, they are local to the macro by definition.

You second grave error is how you call the macro with call execute. Using double quotes lets the macro be resolved immediately, which means it executes before the data step is compiled and run, so it has no access to datastep variables/values at all!

When calling a macro with call execute and dynamic values from a data step, use single quotes at least around the macro name. Then the macro will execute repeatedly with the values from the data step, but results from that macro can never be used inside the data step that call executed it.

If you turn on options mprint mlogic, you can see what happens:

When the data step does the call execute, the macro is resolved, and, aside from trying to %put some variables that do not yet have values, resolves to sql code that is the fed to the call execute queue. Since your data step fails while trying to access not-yet-existing macro variables, the call execute is the never run, and the sql does not execute.

Try this slightly changed code:

%macro distinct_values(in, col1, col2);
%GLOBAL unique_values1 unique_values2 ;
  	proc sql noprint;
    select count(distinct &col1) into :unique_values1
    from &in ;

	select count(unique(&col2)) into :unique_values2
	from &in ;
	quit;

	%PUT DISTINCT VALUES OF COL1: &unique_values1. ;
	%PUT #######################################################;
	%PUT DISTINCT VALUES OF COL2: &unique_values2. ;
%mend;

options mlogic mprint;

%macro test;
data _null_;
call execute("%distinct_values(in=SASHELP.DEMOGRAPHICS,col1=id,col2=iso)");
run;

data _test;
distnct_col1 = &unique_values1.;
distnct_col2 = &unique_values2.;
run;
%mend;
%test

Now, the first data step calls the macro, which resolves to the SQL code. This code is fed to the call execute subroutine. After that data step, the SQL is fetched from the call execute queue and run, filling the macro variables with values. The second data step "catches" those values and stores them into the dataset.

[FK1]

@Kurt_Bremser: Kurt, you are my hero! Thanks a lot for clarifying to me the difference of the double and signle quotation marks in the call execute - statement. I think this also explains, why on first run for a given dataset the variables in "_test" have the wrong values, meaning the values of the former run....

Also, of course, thank you to all the other replies so far. It really helps me a lot!

[Quentin]

Nice explanation @Kurt_Bremser. Note that due to the timing issues you address, &unique_values1 and &unique_values2 both resolve to null on the %PUT statements in the macro. (Assuming they don't already exist in global symbol table) This is because the %PUT statements execute before the SQL step executes.

[Tom]

Wow.  @Kurt_Bremser has explained most of the errors with this example.

If you want to use a dataset to generate a series of macro calls (or other code) you first need to understand the order that things will run.  At a high level you could think of it this way.

1) Run a data step that generates code.

2) Run the code that was generated.

This is what CALL EXECUTE does for you. It pushes the strings it pass it onto the stack for SAS to execute after the current data step finishes.  So don't expect to be able to reference anything in step (1) that is going to be generated when step (2) runs.  

The wrinkle that your code also hit is that when you generate code via CALL EXECUTE that includes macro calls then those calls will run when CALL EXECUTE pushes to SAS and what ends up being pushed onto the stack to execute after the datastep is the generated code and not the macro call.  If the macro has macro logic and macro only statements like %LET and %PUT then those run when the macro executes, but before any of the generated SAS code executes. It can cause a very confusing situation and in general different results than if you ran the same series of macro calls without using CALL EXECUTE.  The fix is to use the %NRSTR() macro function to quote the macro name so that the call to the macro gets pused onto the stack without being executed. Then when SAS finishes step (1) and begins reading back the code stored by CALL EXECUTE it sees the macro call and runs it.  

Let's make a little example using SASHELP.CLASS as are input data to drive step (1).  For each observation we want to call the macro '%MYMACRO' setting the macro parameter NAME to the value of the datastep variable NAME in the macro call.

data _null_;
   set sashelp.class ;
   call execute(catx(' ','%nrstr(%mymacro)(name=',name,');'));
run;

One way to debug if this is working correclty is to look at how SAS displays the lines of lines of code generated by CALL EXECUTE in the log. Those lines will appear with + in front of them. In this case using %NRSTR() makes the lines will look like :

+ %mymacro(name=Alfred);
+ %mymacro(name=Alice);
...