从单个数据集创建多个 SAS 宏变量列表 [英] Create several SAS macro variable lists from single dataset

问题描述

由于宏变量值的长度不能超过 (65534) 的最大长度，因此我无法为所有观察结果创建单个宏变量.我想创建一个宏来遍历我的数据集以生成几个 numeric 列表，我可以将这些列表传递给 proc sql 中的 where 语句.

Since the the length of the value of a macro variable cannot exceed the maximum length of (65534), I can't create a single macro variable for all of my observations. I would like to create a macro to iterate through my data set to generate several numeric lists that I can pass to a where statement in proc sql.

而不是这个:

*proc sql noprint;
    select ID into :ParaList separated by ','
    from work.ID_LIST(**firstobs=1 obs=5000**);
quit;*

*proc sql noprint;
    select ID into :ParaList2 separated by ','
    from work.ID_LIST(**firstobs=5001 obs=10000**);
quit;*

*proc sql noprint;
    select ID into :ParaList3 separated by ','
    from work.ID_LIST(**firstobs=10001 obs=15000**);
quit;*

*proc sql noprint;
    select ID into :ParaList4 separated by ','
    from work.ID_LIST(**firstobs=15001 obs=20000**);
quit;*

我想要类似的东西:

*proc sql noprint;
    select ID into :List1-Last4 separated by ','
    from work.ID_LIST(**firstobs=1 obs=&LASTOBS** BY 5000);
quit;*

我想创建一个宏来循环遍历每 5000 次左右的观察，直到最后一次观察我可以传递到 where 语句中，例如 where id in (&ParaList,&ParaList2,&ParaList3,&参数列表 4).我知道还有其他选择，例如

I'd like to create a macro to loop through every 5000 observations or so until last observation that I can pass into a where statement such as where id in (&ParaList,&ParaList2,&ParaList3,&ParaList4). I know there are alternatives such as

id in (select id from work.table)

但在这种情况下，它不起作用.我正在通过 SAS 查询 Hadoop，除了传递宏变量列表之外没有任何成功.

but in this case, it doesn't work. I am querying Hadoop through SAS and haven't had any success except passing macro variable lists.

推荐答案

您可以轻松地使用数据步骤来生成宏变量.您还应该生成一个调用所有其他宏变量的宏变量.

You could easily use a data step to generate the macro variables. You should also generate a macro variable that calls all of the other macro variables.

%let n_per_list=5 ;
data _null_;
  length idlist $32000;
  length macrolist $1000 ;
  retain macrolist;
  do i=1 to &n_per_list until (eof);
    set id_list end=eof;
    idlist=catx(',',idlist,id);
  end;
  listno+1;
  call symputx(cats('paralist',listno),idlist);
  macrolist=catx(',',macrolist,cats('&','paralist',listno));
  call symputx('paralist',macrolist);
run;

对于将 20 个值分成 5 个一组的简单测试会产生以下结果:

Which for a simple test of 20 values split into groups of 5 yields this result:

151  %put Paralist=%superq(ParaList);
Paralist=&paralist1,&paralist2,&paralist3,&paralist4
152  %put &=Paralist1;
PARALIST1=1,2,3,4,5
153  %put &=Paralist;
PARALIST=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

或者您可以只考虑将代码生成为宏，而不是使用宏变量.这对您可以生成多长时间的列表没有任何限制.您可以尝试弄清楚如何在宏内部打开数据集而不生成任何 SAS 代码，以便宏调用的结果只是值列表.但是将宏定义的源代码生成到文件中然后 %include 文件来定义它会容易得多.

Or you could just look at generating the code into a macro instead of using macro variables. That should not have any limits on how long a list you could generate. You could try to figure out how to open the dataset while inside a macro without generating any SAS code so that the result of the macro call is just the list of values. But it would be much easier to generate the source code for the macro definition to a file and then %include the file to define it.

filename code temp;
data _null_;
  set id_list end=eof;
  file code lrecl=80;
  if _n_=1 then put '%macro paralist;' ;
  else put ',' @ ;
  put id @ ;
  if eof then put / '%mend paralist;' ;
run;

同样平凡的 20 值列表的结果.

Result for same trivial 20 value list.

163  %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is file /.../#LN00043.
164 +%macro paralist;
165 +1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20
166 +%mend paralist;
NOTE: %INCLUDE (level 1) ending.
167  %put %paralist;
1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20

这篇关于从单个数据集创建多个 SAS 宏变量列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！