在从一个表中提取的循环内调用执行以执行宏 [英] call execute inside a loop pulling from one table to execute a macro

问题描述

我现在有下面

%macro sqlloop (event_id);

...lots of code, mostly proc sql segments ...

%mend;

生成输出表(名为 export_table2).我需要能够为另一个表(名为 vars)中的每个值运行此代码数十次.我的试用代码测试我想要它做的事情如下(基本上是手动输入这个 68 行表的前两个值)

that generates an output table (named export_table2). I need to be able to run this code dozens of time for every value in another table (named vars). my trial code testing what I want it to do is below (basically manually typing in the first two values of this 68 row table)

data ;

%let empl_nbr_var = '222';
%let fleet = '7ER';
%let position = 'A'; 
%let base = 'BWI';

%sqlloop(event_id = 1);
run;

data summary_pilots;
set work.export_table2;
run;

data;
%let empl_nbr_var = '111';
%let fleet = '320'; 
%let position = 'B'; 
%let base = 'CHS';

%sqlloop(event_id = 2);

run;

data summary_pilots;
set summary_pilots work.export_table2;
run;

这会将每次执行的最终输出堆叠到一个名为 summary_pilots 的表中.如何在循环中执行此操作，可能使用 call execute 来遍历 vars 的每一行?vars 的列正是我需要的宏变量，我想遍历每一行以分配这些宏变量并再次运行我的 %sqlloop.感谢您的帮助！

This produces the final output of each execution stacked into one table called summary_pilots. How can I do this in a loop, prehaps using call execute to iterate through each row of vars? The columns of vars are exactly what I need for the macro variables, and I want to iterate through every single row to assign those macro variable and run my %sqlloop again. Thanks for the help!

目前正在弄清楚 call execute 的工作原理，看看它在这里有什么帮助，但仍然有点卡住......下面的代码完全按照你的想法工作，将表 vars 中的所有变量打印到日志中.

currently figuring out how call execute works and see how its helpful here but still a bit stuck... code below works exactly as youd think, printing out all the variables in the table vars into the log.

data ;
set work.vars;
call execute( '%put='|| strip(empl_nbr_var)  || ';
%put = ' || strip(fleet) ||';
%put = '|| strip(position) ||'; 
%put = ' || strip(base) ||';' );
run;

我正在尝试使用下面的代码，但由于宏被奇怪地分配，我得到了大量的错误.vars 列中的类型与我希望它们在宏中的类型完全匹配，但看起来这可能是这里的问题?

I am trying to use the below code, but am getting a crazy amount of errors due to the macros being assigned weirdly. The types in the columns of vars match exactly what I want them to be in the macros, but it still looks like that might be the issue here?

data ;
set work.vars;
call execute( '
%let empl_nbr_var =' || strip(empl_nbr_var)  || ';
%let fleet = ' || strip(fleet) ||';
%let position = '|| strip(position) ||'; 
%let base = ' || strip(base) ||';

%sqlloop(event_id = 17);' );
run;

而事件 ID 在这里实际上并不重要，所以我现在只是将其作为随机数.

and the event ID doesnt actually matter here so i just left that as a random number for now.

推荐答案

在数据步骤的中间编写 %LET 语句是没有意义的.宏处理器将在将数据步骤代码的文本传递给 SAS 进行处理之前对它们进行评估.通过在数据步骤之前移动 %LET 语句来避免混淆.

It makes no sense to code %LET statements in the middle of a data step. The macro processor will evaluate them before it passes the text of the data step code to SAS to process. Avoid confusing yourself by moving the %LET statements before the data step.

如果宏需要宏变量(如 FLEET)的值作为输入，则将这些东西作为宏的参数.不要创建引用魔术"的宏；宏变量，既不是输入参数也不是由宏创建的宏变量.相反，对它们的引用只是出现在宏定义的中间，就好像它们的值会以某种方式神奇地出现.

If the macro needs values of macros variables, like FLEET, as input then make those things parameters to the macro. Don't create a macro that references "magic" macro variables, macro variables that are neither input parameters nor created by the macro. Instead the reference to them just appears in the middle of the macro definition as if their values will appear by magic somehow.

%macro sqlloop(empl_nbr_var,fleet,position,base);
  ... code that uses &fleet. 
  
%mend;

如果您希望通过宏运行大量参数组合，请先将它们收集到数据集中.

If you have a lot of combinations of parameters you want run through your macro then collect them into a dataset first.

data inputs ;
  input empl_nbr_var fleet $ position $ base $ ;
cards;
222 7ER A BWI
111 320 B CHS
;

然后您可以使用这些数据集变量来生成对宏的调用.您可以尝试使用 call execute() 来执行此操作，但我个人发现使用数据步骤将代码写入文件要容易得多.然后您可以检查文件并确保代码生成逻辑正确.此外，您还可以使用 PUT 语句的强大功能来简化代码生成.例如，如果变量名称与参数名称匹配，则可以使用命名输出.

Then you can use those dataset variables to generate the calls to the macro. You could try using call execute() to do this, but personally I find it a lot easier to use a data step to write the code to a file. Then you can examine the file and make sure the code generation logic is correct. Plus you can use the power of the PUT statement to make the code generation easier. For example if the variable names match the parameter names you can use named output.

filename code temp;
data _null_;
  set inputs;
  file code ;
  put '%sqlloop(' empl_nbr_var= ',' fleet= ',' position= ',' base= ')';
run;

这将生成如下代码:

%sqlloop(empl_nbr_var=222 ,fleet=7ER ,position=A ,base=BWI )
%sqlloop(empl_nbr_var=111 ,fleet=320 ,position=B ,base=CHS )

一旦您确信它正在生成正确的代码，请使用 %INCLUDE 命令运行它生成的代码.

Once you are confident that it is generating the right code use the %INCLUDE command to run the code it generates.

%include code / source2;

如果宏没有自己的聚合结果步骤，您可以在代码生成中包含该步骤.

If the macro does not have its own step for aggregating the results you could include that step in the code generation.

filename code temp;
data _null_;
  set inputs;
  file code ;
  put '%sqlloop(' empl_nbr_var= ',' fleet= ',' position= ',' base= ')';
  put 'proc append base=summary_pilots data=export_table force; run;' ;
run;
%include code / source2;

这篇关于在从一个表中提取的循环内调用执行以执行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！