CODEWITHSUNDEEP
sassas-macroproc-sqlpass-through
Chuck Ramirez
Chuck Ramirez

Reputation: 255

SAS pass-through facility. How to insert a big list from a local table in a query?

I need to query a large table in a server (REMOTE_TBL) using the SAS pass-through facility. In order to make the query shorter, I want to send a list of IDs extracted from a local table (LOCAL_TBL). My first step is to get the IDs into a variable called id_list using an INTO statement:

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

Then I pass these IDs to the pass-through query:

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 code runs fine except that I get the following message:

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

Is there an easier way to send the selected ID's to the pass-through query? Is there a way to store the selected ID's in two or more variables?

Upvotes: 0

Views: 1806

Answers (4)

user667489
user667489

Reputation: 9569

Another option - write out the query to a temporary file and then %include it. No macro logic needed!

proc sort 
  data = WORK.LOCAL_TBL(keep = ID) 
  out = distinct_ids 
  nodupkey;
run;

data _null_;
  set distinct_ids end = eof;
  file "%sysfunc(pathname(work))/temp.sas";
  if _n_ = 1 then put "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 (" @;
  put ID @;
  if not(eof) then put "," @;
  if eof then put ");QUIT;" @;
  put;
run;

/*Use nosource2 to avoid cluttering the log*/
%include "%sysfunc(pathname(work))/temp.sas" /nosource2;

Upvotes: 0

Richard
Richard

Reputation: 27508

You are passing many data values that appear in your IN (…) clause. The number of values allowed varies by data base; some may limit to 250 values per clause and the length of a statement might have limitations. If the macro variable creates a list of values 20,000 characters long, the remote side might not like that.

When dealing with a lookup of perhaps > 100 values, take some time first to communicate your need to the DB admin for creating temporary tables. When you have such rights, your queries will be more efficient remote side.

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

If you can't get the proper permissions, you would have to chop up the id list into pieces and have a macro create a series of ORed IN searches.

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

For example:

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

...

Upvotes: 1

Tom
Tom

Reputation: 51611

Store the values into multiple macro variables and then store the names of the macro variables into another macro variable.

So this code will make a series of macro variables named M1, M2, .... and then set ID_LIST to &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;

Then when you expand ID_LIST the macro processor will expand all of the individual Mx macro variables. This little data step will create a couple of example macro variables to demonstrate the idea.

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

Results:

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

Upvotes: 2

Vojta F
Vojta F

Reputation: 564

I suggest that you first save all the distinct values into a table, and then (again using proc sql + into) load the values into a few stand-alone macrovariables, reading the table several times in a few sets; indeed they have to be mutually exclusive yet jointly exhaustive.

Do you have access to and CREATE privileges in the DB where your dbo.REMOTE_TBL resides? If so you might also think about copying your WORK.LOCAL_TBL into a temporary table in the DB and run an inner join right there.

Upvotes: 0

Related Questions