Macro variables issue when using call execute

Question

I have 2 macros below that I am trying to execute 1 after another like a loop using a metadata table and the call execute command in a data step.

macro %TWO requires the global variable &names_agg. that is supposed to be created in macro %ONE. However in the below code, &names_agg is blank the first time I run it. If I run it again, it will only keep the value from the last time it is run.

The idea is that every time %ONE is run, a new &names_agg. is created.

What am I doing wrong?

Thanks

%macro ONE(condition); 
%global names_agg;  
%let names_agg = ; 

proc sql;
    select 
        cats(name,"_agg"),
    into 
        :names_agg separated by " ",
    from dataset
    where condition = "&condition."
    ;
quit;
%mend;

%macro TWO(name_OT);

data &name_OT.;
    set &names_agg.;
run;

%mend;

data _null_;
  length code $32767;
  set meta_table;
  code = "%ONE(" || cats(condition) || "); %TWO(" || cats(Name_OT) || ");";
  call execute(code);
run;

Sorry about the messy log, this is the actual code. The problem is with NAMES_AGG_A _B and _C no resolving correctly

871  data test;
872    length code $32767;
873    set c.new_name_OT (obs=1);
874    code = '%OT_Append(' || cats(portfolio) || ',' || cats(scorecard) || ',' ||
874! cats(event_table) || ',' ||
875          cats(scorecard_type) || ',' || cats(obs_period) || ',' || cats(outcome_period) ||
875! ',' || cats(x_var) ||
876          ',' || cats(y_var) || ',' || cats(use) || ',' || cats(condition) || '); %put
876! &names_agg_a.; %OT_Append2(' || cats(Name_OT) || ');';
877    call execute(code);
878  run;

MLOGIC(OT_APPEND):  Beginning execution.
MLOGIC(OT_APPEND):  Parameter PORTFOLIO has value MTG
MLOGIC(OT_APPEND):  Parameter SCORECARD has value A
MLOGIC(OT_APPEND):  Parameter EVENT_TABLE has value event_table_name
MLOGIC(OT_APPEND):  Parameter SCORECARD_TYPE has value Application
MLOGIC(OT_APPEND):  Parameter OBS_PERIOD has value 1
MLOGIC(OT_APPEND):  Parameter OUTCOME_PERIOD has value 18
MLOGIC(OT_APPEND):  Parameter X_VAR has value PI
MLOGIC(OT_APPEND):  Parameter Y_VAR has value GB_Odds
MLOGIC(OT_APPEND):  Parameter USE has value Development
MLOGIC(OT_APPEND):  Parameter CONDITION has value
MLOGIC(OT_APPEND):  %LET (variable name is NAMES_AGG_A)
MLOGIC(OT_APPEND):  %LET (variable name is NAMES_AGG_B)
MLOGIC(OT_APPEND):  %LET (variable name is NAMES_AGG_C)
MPRINT(OT_APPEND):   proc sql;
SYMBOLGEN:  Macro variable PORTFOLIO resolves to MTG
SYMBOLGEN:  Macro variable SCORECARD resolves to A
SYMBOLGEN:  Macro variable EVENT_TABLE resolves to event_table_name
SYMBOLGEN:  Macro variable SCORECARD_TYPE resolves to Application
SYMBOLGEN:  Macro variable OBS_PERIOD resolves to 1
SYMBOLGEN:  Macro variable OUTCOME_PERIOD resolves to 18
SYMBOLGEN:  Macro variable X_VAR resolves to PI
SYMBOLGEN:  Macro variable Y_VAR resolves to GB_Odds
SYMBOLGEN:  Macro variable USE resolves to Development
SYMBOLGEN:  Macro variable CONDITION resolves to
MPRINT(OT_APPEND):   select cats("c.",name,"_agg_a"), cats("c.",name,"_agg_b"),
cats("c.",name,"_agg_c") into :names_agg_a separated by " ", :names_agg_b separated by " ",
:names_agg_c separated by " " from c.datasets_pit where portfolio = "MTG" and scorecard = "A"
and event_table = "event_table_name" and scorecard_type = "Application" and obs_period = 1 and
outcome_period = 18 and x_var = "PI" and y_var = "GB_Odds" and use = "Development" and
condition = "" ;
MPRINT(OT_APPEND):   quit;
MLOGIC(OT_APPEND):  Ending execution.
SYMBOLGEN:  Macro variable NAMES_AGG_A resolves to

Essentially the problem is here, the above put statement in the call execute shows that NAMES_AGG_A resolves to nothing.

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

MLOGIC(OT_APPEND2):  Beginning execution.
MLOGIC(OT_APPEND2):  Parameter NAME_OT2 has value MTG_Dev_OT_1
SYMBOLGEN:  Macro variable NAME_OT2 resolves to MTG_Dev_OT_1
MPRINT(OT_APPEND2):   data c.MTG_Dev_OT_1_ODDS;
SYMBOLGEN:  Macro variable NAMES_AGG_A resolves to
MPRINT(OT_APPEND2):   set ;
MPRINT(OT_APPEND2):   run;
SYMBOLGEN:  Macro variable NAME_OT2 resolves to MTG_Dev_OT_1
MPRINT(OT_APPEND2):   data c.MTG_Dev_OT_1_GINI;
SYMBOLGEN:  Macro variable NAMES_AGG_B resolves to
MPRINT(OT_APPEND2):   set ;
MPRINT(OT_APPEND2):   run;
SYMBOLGEN:  Macro variable NAME_OT2 resolves to MTG_Dev_OT_1
MPRINT(OT_APPEND2):   data c.MTG_Dev_OT_1_DIST;
SYMBOLGEN:  Macro variable NAMES_AGG_C resolves to
MPRINT(OT_APPEND2):   set ;
MPRINT(OT_APPEND2):   run;
MLOGIC(OT_APPEND2):  Ending execution.
NOTE: There were 1 observations read from the data set C.NEW_NAME_OT.
NOTE: The data set WORK.TEST has 1 observations and 12 variables.

NOTE: CALL EXECUTE generated line.
1   +       proc sql;
1   +                         select             cats("c.",name,"_agg_a"),
cats("c.",name,"_agg_b"),             cats("c.",name,"_agg_c")         into
:names_agg_a separated by " ",             :names_agg_b separated by " ",
2   + :names_agg_c separated by " "         from c.datasets_pit             where portfolio =
"MTG" and                 scorecard = "A" and                 event_table = "event_table_name"
and                 scorecard_type = "Application" and
3   + obs_period = 1 and                 outcome_period = 18 and                 x_var = "PI"
and                 y_var = "GB_Odds" and                 use = "Development" and
  condition = ""         ;     quit;; data c.MTG_Dev_OT_1_ODDS;     set
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


4   + ; run;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set C.MTG_DEV_OT_1_ODDS has 1 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


4   +         data c.MTG_Dev_OT_1_GINI;     set ; run;

NOTE: There were 1 observations read from the data set C.MTG_DEV_OT_1_ODDS.
NOTE: The data set C.MTG_DEV_OT_1_GINI has 1 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


4   +                                                   data c.MTG_Dev_OT_1_DIST;     set ; run;

NOTE: There were 1 observations read from the data set C.MTG_DEV_OT_1_GINI.
NOTE: The data set C.MTG_DEV_OT_1_DIST has 1 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Chang Chung · Accepted Answer · 2010-12-02T16:33:45.840

You can delay macro invocations with %nrstr(), then it works fine.

   /* test data */
   data dataset;
     name="a"; condition="1"; output;
     name="b"; condition=" "; output;
     name="c"; condition="1"; output;
   run;

   data a_agg; v="a_agg"; run;
   data b_agg; v="b_agg"; run;
   data c_agg; v="c_agg"; run;

   data meta_table;
     condition="1"; name_ot="ot1"; output;
     condition="2"; name_ot="ot2"; output;
     condition=" "; name_ot="ot_"; output;
   run;

   %macro one(condition); 
     %global names_agg;  
     %let names_agg = ; 
     proc sql noprint;
       select cats(name,"_agg") into :names_agg separated by " "
       from dataset where condition = "&condition.";
     quit;
   %mend;

   %*-- just checking --*;
   %one(condition=1) %put names_agg=&names_agg;
   %one(condition=2) %put names_agg=&names_agg;
   %one(condition= ) %put names_agg=&names_agg;
   %*-- on log
   names_agg=a_agg c_agg
   names_agg=
   names_agg=b_agg
   --*;

   %macro two(name_ot);
     %if &names_agg= %then %do;
       data &name_ot.; run;
     %end; %else %do;
       data &name_ot.;
         set &names_agg.;
       run;
     %end;
   %mend;

  data _null_;
      length code $200;
      set meta_table;
      code = catt('%one(', condition, ")");
      code = catt(code, '%two(', name_ot, ")");
      code = catt('%nrstr(', code, ")");
      call execute(code);
   run;

   /* check */
   title ot1; proc print data=ot1; run; title;
   /* on lst
   ot1
   Obs      v
    1     a_agg
    2     c_agg
   */
   title ot2; proc print data=ot2; run; title;
   /* on log
   NOTE: No variables in data set WORK.OT2.
   */
   title ot_; proc print data=ot_; run; title;
   /* on lst
   ot_
   Obs      v
    1     b_agg
   */

Thanks Chang! I just added %nrstr to my code and it has solved my problem! — MarkG, Dec 08 '10 at 23:32

score 1 · Answer 2 · edited Oct 25 '11 at 01:27

1

You probably need to change the double quotes to single quotes in your datastep like so:

data _null_;
  length code $32767;
  set meta_table;
  code = '%ONE(' || cats(condition) || '); %TWO(' || cats(Name_OT) || ");";
  call execute(code);
run;

Right now the macro processor is trying to resolve the percentage symbols in the 3rd line. You can stop it from doing this by hiding them using single quotes.

edited Oct 25 '11 at 01:27

Triad sou.

2,969
3
23
27

answered Nov 11 '10 at 16:11

Robert Penridge

8,424
2
34
55

Hi Rob, I tried using single quotes as you suggested, but the problem still exists. Basically &names_agg does not get created before macro TWO runs. Thanks for your answer though! – MarkG Nov 11 '10 at 22:25

score 0 · Answer 3 · answered Nov 11 '10 at 09:54

Unless you've pared back a lot from the macros for the example you've posted, it's difficult to see why you would do this with two macros, rather than just one (or indeed why you'd use macros to do this at all) like this:

%macro TheOnlyOne(condition,name_OT);
  proc sql noprint;
    select cats(name,"_agg")
    into :names_agg separated by " "
    from dataset
    where condition="&condition";
  quit;
  data &name_OT;
    set &names_agg;
  run;
%mend;

Anyway, wrt your question about what's going on with the macro variables between calls etc., have you tried

Running the macros sequentially outside of the call execute method?
Setting options mprint mlogic symbolgen; before execution to see debugging info in the log?
Using some %put statements in your macros, and put statements in your call execute datastep, in order to see what's being generated at various points?

It's recommended when developing macro applications to first get the code running without using macro at all, then adding macro variables and explicitly %let-ting their values, then testing it in the context of a macro. Moving to call execute would be after that.

Perhaps try a few of the above points and come back with some log output we can debug. There are a couple of other errors/issues in the code you've posted but rather than pointing them out I'm assuming you're paring it back for the SO post.

BTW I like the idea of driving data-driven code using data _null_ with call execute, I use this approach a lot.

Hi sasfrog, I did initially use a single macro which works when just running the macro normally, but inside the call execute the &name_agg variable is empty the first time it runs, but has the value of the last run if run again. — MarkG, Nov 11 '10 at 22:56
For this reason I split it into 2 macros thinking it will resolve &names_agg when macro ONE ends so it can be used in macro TWO. Again this runs fine normally, but the same problem exists in the call execute. It seems that the &name_agg variable will only resolve at the end of the call execute, i.e. it may have run the macros x times depending on what I specify in the metadata table but will only generate the last &name_agg after the all macros have been completed. — MarkG, Nov 11 '10 at 23:09
@MarkG, all I can suggest is that you use the macro debugging techniques listed in my post, and perhaps run the `data _null_` step with `obs=1` so it just runs once. Then we'll have some log to examine. And definitely do what @Rob suggests with single quotes. — sasfrog, Nov 11 '10 at 23:54
Thanks for your help sasfrog, I've posted the log as an answer below. — MarkG, Nov 12 '10 at 00:35

Macro variables issue when using call execute

3 Answers3