Dynamically create in statement

[deleted_user]

I'm trying to work around the Oracle 1,000 item limit for an IN statement and want to dynamically create multiple in statements of 1,000 (or less) items separated by an OR.
I have a table containing a field called gpi_nbr among other fields. There are currently a total of 4,623 rows in this table. I will pull a subset of rows from this table by filtering on one or more of the other fields. In one case I will pull 2,500 rows. So I need to create 3 lists, two containing 1,000 gpi_nbr and the third containing the remaining 500 gpi_nbr.
These lists will be used to query another dataset with a filter like...
...
and (gpi_nbr in (&list1) or gpi_nbr in (&list2) or gpi_nbr in (&list3))
...

I've been able to create a single list using the following code...

proc sql noprint;
select '"'||gpi_nbr||'"'
into :varlist separated by ','
from WH_TEMP;
quit;

%let cntlist = &sqlobs;
%let nbrlists = %sysevalf(&cntlist/1000, ceil);
%put &cntlist;
%put &nbrlists;
%put &varlist;

cntlist tells me how many rows I am returning
nbrlists tells me how many lists i need to create

I'm relatively new to sas coding and just can't figure out what to do next. I know I need to do something n times (in this case n=3=nbrlists).

[Flip]

Wouldn't it be simpler to use a subquery for your IN statement? Or an EXISTS?

[deleted_user]

Most often the list data will come from a different database than the data I want to filter. It was my understanding that if you cross databases, the entire dataset would be pulled down to the sas server before the subquery would be applied. When looking at a years worth of data I'm in the 21-30 million record range. Is that correct? If not, I'd be thrilled to handle this using sql.

[Flip]

Let ORACLE do the work. Run it as a passthrough. If the lookup table is in SAS create a temporary table in ORACLE and do the subsetting there like

create table big as select * from connection to oracle (select .....
.. where var in (select var from temptable));