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