代码之家 › 专栏 › 技术社区 › J_Lard

proc sql“select into”不创建宏变量

sas-macro sas

J_Lard · 技术社区 · 8 年前

我遇到了一个问题,创建宏变量时使用 proc sql select into : 逻辑。代码看起来非常简单,但我应该注意,导致问题的宏是从其他几个宏调用的。

下面是导致问题的代码片段。

%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;

由于某种原因,变量 totep , totexpt , partep 和 pqrtexpt 没有被创建,我在日志中找不到任何有用的信息,这些信息可能会让情况变得明朗起来。

这里是日志的一部分,包括来自 _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

如果我移除 noprint 来自 proc sql 语句,然后输出正确的值,所以我不确定发生了什么。任何帮助都将不胜感激。

1 回复 | 直到 8 年前

Joe 8 年前

您的问题几乎肯定与执行时间有关。尤其是在使用时 call execute 如果宏变量在给定有用值之前由SAS处理器解决,则很容易遇到计时问题。

下面是一个简单的例子。我专门创建了一个数据集,这样您就可以看到何时发生了这种情况(这不是必要的步骤,也不影响示例,它只是创建了一个有用的日志条目)。

请注意,当宏单独运行时,一切都按预期工作;但当它与 调用执行 这个 %put 执行之前宏实际执行。

第三个示例使用 %nrstr 强制SA在实际运行宏之前不尝试解析宏-这会导致正确提交宏。

实际上,第一个 调用执行 版本让sas处理宏文本,然后将其提交到sas.exe,这是您真正不想要的。添加 %NRSTR 解决了这个问题。

%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;