If Doc’s first option is not possible, in my experience, Doc’s third option usually works well. If you do go with option 3, I highly recommend that you also utilize an Oracle hint to ensure that Oracle uses the correct index. When I have left off the Oracle hint, I have had cases where a particular step executes in less than 10 seconds one day and more than 10 minutes the next. Here is a useful paper that contains information on identifying and using Oracle indexes, as well as many other tips on using SAS to extract data from Oracle databases:
www.nesug.org/proceedings/nesug05/io/io8.pdf
Here is some sample code that illustrates option three. The code assumes that ORALIB.ORATBL is an oracle table, SASLIB.SASTBL is a local table, the two tables are being merged together based on the variable VBL, and the ORATBL table contains an index VBL_INDX that uses VBL as a key variable:
[pre]
PROC SQL NOPRINT;
SELECT VBL INTO: VBL_VALUES SEPARATED BY '", "'
FROM SASLIB.SASTBL;
QUIT;
DATA SASLIB.MERGED_TBL;
SET ORALIB.ORATBL (ORHINTS='/*+ INDEX(ORATBL, VBL_INDX) */');
WHERE VBL IN ("&VBL_VALUES");
RUN;
[/pre]