PROC SQL "SELECT INTO" does not create macro variable

Question

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.

does %put TOTEXP: &totexp; after the quit; output anything? — DomPazz
– DomPazz, Commented May 21, 2018 at 17:55
@DomPazz No, I just get the same waning symbolic reference not resolved. — J_Lard
– J_Lard, Commented May 21, 2018 at 17:59
If the macro variables were declared or extant in a containing scope and are trying to be logged within an invoked macro the %put _local% will not log them because they are not local to the invoked scope. Also, try adding undocumented _tree and _method options to see more detail in the log proc sql noprint _tree _method; NOPRINT should not affect INTO. — Richard
– Richard, Commented May 21, 2018 at 18:30
What's the timing here? Are you using CALL EXECUTE to run this, for example? I think that's almost certainly related. — Joe
– Joe, Commented May 21, 2018 at 18:49
@Richard and Dom - the way I could tell what the issue was here is that the %put did do something, it just didn't do it at the right time. You can see it output the local macro variables in the log just a ways up from the rest of it. — Joe
– Joe, Commented May 21, 2018 at 19:13

Joe · Accepted Answer · 2018-05-21 19:07:05Z

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;

This is perfect, I never would have thought to add %nrstr.

Collectives™ on Stack Overflow

PROC SQL "SELECT INTO" does not create macro variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related