Why does using CALL EXECUTE to run a macro error when running it directly works?

Question

I recently inherited a SAS program that looks something like this:

%MACRO ComplicatedStuff( GroupId= );

    %LET FileId = %SYSFUNC( OPEN( Work.BigDataSet ) );

    %PUT    'Doing something really difficult with ' &GroupId.;

    %LET CloseRC = %SYSFUNC( CLOSE( &FileId. ) );

%MEND ComplicatedStuff;

%ComplicatedStuff(GroupId=ABC1);
%ComplicatedStuff(GroupId=DEF2);
%ComplicatedStuff(GroupId=3GHI);
%ComplicatedStuff(GroupId=J4KI);

Being a multi-faceted programmer, I looked at this and thought "surely I can make this a least a little bit more dynamic". Sure enough, I was able to develop what I thought was a simple solution using CALL EXECUTE:

DATA Work.IDs;

    INPUT   ID      $4.
            ;

DATALINES;
ABC1
DEF2
3GHI
J4KI
RUN;

DATA Work.CommandDebug;
    SET Work.IDs;

    Command = CATS(
                '%ComplicatedStuff(GroupId=', ID, ');'
              );

    CALL EXECUTE( Command );

RUN;

I was happy with this solution until it came time to FTP the files generated by ComplicatedStuff to a different server. Our SAS server is running on Unix and the SAS admins created a helpful little macro for us to call named %sas_sftp (because, I've been told, the x code gets really ugly). Unfortunately, I cannot post the %sas_sftp code - it belongs to my company and I don't think they want it on SO.

I attempted to call the %sas_sftp macro just as I had called the %ComplicatedStuff macro (both as a second CALL EXECUTE within the same data step, and as a second data step), but only the first file (of about 30) would make it to the destination. When I looked at the log, it looked like the second macro started executing before the ftp had finished (the ftp pipe, or whatever it is, hadn't been released before the next ftp started), so the subsequent ftps were simply silently failing due to resource unavailability (I presume).

I thought that the EXECUTE would basically queue up my macro calls and then execute them as if they were sequentially located in the code (as they originally were) - one at a time. Clearly something else is going on because, while the first approach above worked without issue, my dynamic solution failed. I poured over CALL EXECUTE: How and Why and the SAS documentation, but I'm afraid I just don't understand what they are talking about.

I did eventually find a work around (or rather, a colleague found one) which I posted below as an "answer", but I'd really like someone to explain the EXECUTE function and how it works.

Why didn't my first attempt, using CALL EXECUTE, work?

Answer 1

CALL EXECUTE works similarly to your code in the community wiki, except for some specific issues relating to timing. The most common issue I run into is when I'm doing a macro that includes something that defines a macro variable, such as a PROC SQL select into inside that macro which then creates macro text used in the macro - not dissimilar to your answer. Because of timing rules, that isn't executed until after the CALL EXECUTE finishes constructing the code to be executed, which means the value isn't changed properly inside the code.

Here is an example.

%macro mymacro(age=0);
proc sql noprint;
select quote(name) into :namelist separated by ',' from sashelp.class where age=&age.;
quit;

data name_age;
set sashelp.class;
where name in (&namelist.);
run;
proc print data=name_age;
var name age;
run;
%mend mymacro;

proc sort data=sashelp.class out=class nodupkey;
by age;
run;

OK, now I have a control dataset (class) and a macro to run off it. Here is the call execute. This does not work properly; the first time it runs you will get messages about &namelist being undefined, the second and future times you will get all age=16 (the last age) since that is what the macro variable is defined as.

data _null_;
set class;
exec_val = cats('%mymacro(age=',age,')');
call execute(exec_val);
run;

Here is the sql macro call. This works as expected.

proc sql noprint;
select cats('%mymacro(age=',age,')') into :calllist separated by ' '
    from class;
quit;   
&calllist;

I don't find call execute to be as useful as the PROC SQL macro list solution to data generated code, except when it's easier to construct the code in a data step and I'm not doing anything that causes timing issues.

Answer 2

This is a work around which avoids the EXECUTE function. I'm posting it as an aid to future visitors, but it doesn't really answer my core question.

The code below creates macro variables with the commands I want to execute by taking advantage of the SQL INTO: syntax. I then create a simple macro which basically iterates over the macro variables and resolves them (causing the statements to be executed as if they were literally in the source code).

PROC SQL NOPRINT;
    SELECT      COUNT(*)
    INTO        :CommandCount
    FROM        Work.CommandDebug
    ;

    SELECT      Command
    INTO        :Command1 - :Command%LEFT(&CommandCount.)
    FROM        Work.CommandDebug
    ;
QUIT;

%MACRO ExeCommands;

    %DO I = 1 %TO &CommandCount.;

        &&Command&I.; /* Resolves to %ComplicatedStuff(GroupId=ABC1);, etc */

    %END;

%MEND;
%ExeCommands;

Answer 3

A good work around for macro evaluation timing in CALL EXECUTE is to put the code into a temporary file and then include the file. This is also useful for debugging when you want to have a hard copy of the submitted code (just switch the fileref to a physical file).

%macro mymacro(age=0);
proc sql noprint;
select quote(name) into :namelist separated by ',' from sashelp.class where age=&age.;
quit;

data name_age;
set sashelp.class;
where name in (&namelist.);
run;
proc print data=name_age;
var name age;
run;
%mend mymacro;

proc sort data=sashelp.class out=class nodupkey;
by age;
run;

filename blah temp;

data _null_;
set class;
file blah;
exec_val = cats('%mymacro(age=',age,')');
put exec_val;
run;

%include blah;

filename blah clear;