PROC SQL "SELECT INTO" does not create macro variable

I've run into an issue creating macro variables using proc sql select into : logic. The code seems to be pretty straightforward, but I should note that the macro that is causing problems is called from several other macros.

Here is the snippet that is causing issues.

%do year_num=1 %to 5;
    %if &year_num=1 %then %let min_date = %eval(&max2.-17);
    %else %let min_date = %eval(&min_date.-12);

    data tmp;
    set bf(firstobs=&min_date obs=%eval(11+&min_date));
    run;

    data tmp2;
    set bf(firstobs=%eval(5+&min_date) obs=%eval(7+&min_date));
    run;

    proc sql noprint;
    select sum(EP), sum(ExpectedLoss)
      into :totep, :totexpt
    from tmp;

    select sum(EP), sum(ExpectedLoss)
      into :partep, :partexpt
    from tmp2;
    quit;

    %put _LOCAL_;

    *Other code...;

%end;

For some reason, the variables totep, totexpt, partep and pqrtexpt are not getting created and I can't find any useful information in the log that may shed light on the situation.

Here is part of the log, including the output from _LOCAL_.

SYMBOLGEN:  Macro variable YEAR_NUM resolves to 1
SYMBOLGEN:  Macro variable MAX2 resolves to 96
MPRINT(BFMETHOD):   data tmp;
SYMBOLGEN:  Macro variable MIN_DATE resolves to 79
SYMBOLGEN:  Macro variable MIN_DATE resolves to 79
MPRINT(BFMETHOD):   set bf(firstobs=79 obs=90);
MPRINT(BFMETHOD):   run;
MPRINT(BFMETHOD):   data tmp2;
SYMBOLGEN:  Macro variable MIN_DATE resolves to 79
SYMBOLGEN:  Macro variable MIN_DATE resolves to 79
MPRINT(BFMETHOD):   set bf(firstobs=84 obs=86);
MPRINT(BFMETHOD):   run;
MPRINT(BFMETHOD):   proc sql noprint;
MPRINT(BFMETHOD):   select sum(EP), sum(ExpectedLoss) into :totep, :totexpt from tmp;
MPRINT(BFMETHOD):   select sum(EP), sum(ExpectedLoss) into :partep, :partexpt from tmp2;
MPRINT(BFMETHOD):   quit;
BFMETHOD I 12
BFMETHOD DSET all
BFMETHOD SEAS_MIN 0.6
BFMETHOD YEAR_NUM 1
BFMETHOD SEAS_MAX 1.66666666666666
BFMETHOD MIN_DATE 79

data tmp;     set bf(firstobs=79 obs=90);     run;

NOTE: There were 12 observations read from the data set WORK.BF.
NOTE: The data set WORK.TMP has 12 observations and 35 variables.
NOTE: Compressing data set WORK.TMP increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

109       +    data tmp2;     set bf(firstobs=84 obs=86);     run;

NOTE: There were 3 observations read from the data set WORK.BF.
NOTE: The data set WORK.TMP2 has 3 observations and 35 variables.
NOTE: Compressing data set WORK.TMP2 increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


109       +                                                             proc sql noprint;
109       +                                                                                   select sum(EP), sum(ExpectedLoss)       into :totep, :totexpt     from tmp;
109       +
     select sum(EP), sum(ExpectedLoss)       into :partep, :partexpt     from tmp2;
109       +
     quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

If I remove noprint from the proc sql statement then the correct values are output so I'm not sure what's going on. Any help would be appreciated.

Solution

Your issue is almost certainly related to execution timing. Particularly when using call execute, you can easily run into problems with timing where the macro variable is resolved by the SAS processor earlier than it's given a useful value.

Here's a simplified example. I specifically make a dataset so you can see when that happens (it's not a necessary step nor does it affect the example, it just makes a useful log entry).

Notice how when the macro runs on its own, everything works how you'd expect; but when it's run with call execute the %put executes before the macro actually executes.

The third example uses %nrstr to force SAS to not try to resolve the macro before actually running it - which causes it to be submitted properly.

Effectively, the first call execute version has SAS process the macro text then submit it to sas.exe - which you do not really want. Adding %nrstr fixes that.

%macro do_something();
    %local mlist;
    data class_m;
      set sashelp.class;
      where sex='M';
    run;

    proc sql;
      select name into :mlist separated by ' '
      from class_m;
    quit;

    %put &=mlist;
%mend do_something;

%put Macro run on its own;
%do_something;


%put Macro run via call execute;
options mprint symbolgen;
data _null_;
  set sashelp.class;
  if _n_=1 then call execute('%do_something()');
  stop;
run;

%put Macro run with nrstr and call execute;
data _null_;
  set sashelp.class;
  if _n_=1 then call execute('%nrstr(%do_something())');
  stop;
run;