SAS直通设施。如何在查询中插入来自本地表的大列表？

Question

我需要使用SAS through工具查询服务器(REMOTE_TBL)中的一个大表。为了使查询更简短，我想发送一个从本地表(LOCAL_TBL)提取的ID列表。我的第一步是使用INTO语句将is放入名为id_list的变量中：

select distinct ID into: id_list separated by ',' from WORK.LOCAL_TBL 

然后将这些ID传递给直通查询：

PROC SQL;
CONNECT TO sybaseiq AS dbcon
(host="name.cl" server=alias db=iws user=sas_user password=XXXXXX);

create table WANT as 
select * from connection to dbcon(
  select * 
  from    dbo.REMOTE_TBL
  where   ID in (&id_list)
);
QUIT;

代码运行得很好，除了我收到以下消息：

The length of the value of the macro variable exceeds the maximum length

是否有更简单的方法将选定的ID发送到直通查询？有没有办法将选定的ID存储在两个或更多的变量中？

Answer 1

将值存储到多个宏变量中，然后将宏变量的名称存储到另一个宏变量中。

因此，此代码将生成一系列名为M1、M2、...的宏变量。然后将ID_LIST设置为&M1，&M2....

data _null_;
length list $20200 mlist $20000;
do until(eof or length(list)>20000);
  set LOCAL_TBL end=eof;
  list=catx(',',list,id);
end;
call symputx(cats('m',_n_),list);
mlist=catx(',',mlist,cats('&m',_n_));
if eof then call symputx('id_list',mlist);
run;

然后，当您展开ID_LIST时，宏处理器将展开所有单独的Mx宏变量。这个小小的数据步骤将创建两个示例宏变量来演示这一想法。

data _null_;
  call symputx('m1','a,b,c');
  call symputx('m2','d,e,f');
  call symputx('id_list','&m1,&m2');
run;

结果：

70    %put ID_LIST=%superq(id_list);
ID_LIST=&m1,&m2
71    %put ID_LIST=&id_list;
ID_LIST=a,b,c,d,e,f

Answer 2

您正在传递出现在IN (…)子句中的许多数据值。允许的值的数量因数据库而异；有些值可能限制为每个子句250个值，并且语句的长度可能有限制。如果宏变量创建了一个长度为20,000个字符的值列表，则远程端可能不喜欢这样。

在处理可能超过100个值的查找时，请先花些时间将创建临时表的需求告知数据库管理员。当你拥有这样的权限时，你的查询将会更有效的远程端。

… upload id values to #myidlist … 
create table WANT as 
select * from connection to dbcon(
  select * 
  from    dbo.REMOTE_TBL
  where   ID in (select id from #myidlist)
);
QUIT;

如果您不能获得适当的权限，您将不得不将id列表切成碎片，并使用宏来创建一系列ORed IN搜索。

1=0
OR ID IN ( … list-values-1 … )
… 
OR ID IN ( … list-values-N … )

例如：

data have;
  do id = 1 to 44;
    output;
  end;
run;

%let IDS_PER_MACVAR = 10;  * <---------- make as large as you want until error happens again;

* populated the macro vars holding the chopped up ID list;
data _null_;
  length macvar $20;    retain macvar;
  length macval $32000; retain macval;
  set have end=end;

  if mod(_n_-1, &IDS_PER_MACVAR) = 0 then do;

    if not missing(macval) then call symput(macvar, trim(macval));
    call symputx ('VARCOUNT', group);

    group + 1;
    macvar = cats('idlist',group);
    macval = '';
  end;

  macval = catx(',',macval,id);

  if end then do;
    if not missing(macval) then call symput(macvar, trim(macval));
    call symputx ('MVARCOUNT', group);
  end;
run;

* macro that assembles the chopped up bits as a series of ORd INs;
%macro id_in_ors (N=,NAME=);
   %local i;

1 = 0

   %do i = 1 %to &N;
OR ID IN (&&&NAME.&i)
   %end;

%mend;

* use %put to get a sneak peek at what will be passed through;
%put %id_in_ors(N=&MVARCOUNT,NAME=IDLIST);


* actual sql with pass through;
...

create table WANT as 
select * from connection to dbcon(
  select * 
  from    dbo.REMOTE_TBL
  where   ( %ID_IN_ORS(N=&MVARCOUNT,NAME=IDLIST) )  %* <--- idlist piecewise ors ;
);

...    

Answer 3

我建议您首先将所有不同的值保存到一个表中，然后(再次使用proc sql + into)将这些值加载到几个独立的宏变量中，在几个集合中读取表几次；实际上，它们必须是相互排斥的，但必须是联合详尽的。

您是否拥有对dbo.REMOTE_TBL所在数据库的访问权限和创建权限？如果是这样的话，您还可以考虑将WORK.LOCAL_TBL复制到DB中的一个临时表中，并在那里运行一个内部连接。