Passing a list of values to Oracle

[vtguy22]

Hello,

  I am having a problem with passing a list of values, currently in a SAS dataset, to Oracle in an IN clause.  While doing that with a SELECT in the IN the code runs but it never finishes.  I have tried using a join instead with "multi_datasrc_opt = IN_CLAUSE" that works until I join a third table (I have about 7 to join), then it never returns as well.  

  I was hoping there is a SAS equivalent of Oracles EXECUTE IMMEDIATE where I could pass through a query in a string to Oracle directly, but so far I haven't found it.

  Sorry if this is a newbie question, but I've been hitting my head against a wall for 2 weeks trying to figure it out..

Thanks,
Paul.

[Sven111]

Could you post some sample code/data?  Are you using explicit or implicit pass-through SQL or using it through a LIBNAME connection on DATA steps?  There are a lot of odd things I've run across on the interface between Oracle and SAS, but I'd need more info to help.

In general I've found with most DB's it's best to push the data to all one location, either do everything in the DB using explicit pass-through or pull everything into SAS and work with it in datasets, but there are always exceptions to this as well.

[ballardw]

You might post example code starting with connection to see if any of the Oracle users will recognize a problem.

[vtguy22]

Here's one of the variations I've tried.  I've also tried a passthrough, as well as joining to my_sas_table instead of using an IN.  The sas table is just for testing, in the real program that will by dynamically built from a prompt.

LIBNAME mylib ORACLE PATH=mypath SCHEMA=myschema AUTHDOMAIN="mydomain" multi_datasrc_opt = IN_CLAUSE;

%global save_myvariable;

DATA my_sas_table;
INPUT receipt_number $13;
DATALINES;
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
ABC1700150001
;
RUN;

PROC SQL;

CREATE TABLE work.temp2 as
SELECT DISTINCT
t1.FILE_CODE,
t2.MAIN_FILE,
t3.LAST_ACTION
FROM
mylib.FILE_TABLE t1
LEFT JOIN mylib.FILE_TABLE t2 ON t1.FILE_ID = t2.MAIN_FILE_ID
LEFT JOIN mylib.FILE_DATES t3 ON t2.MAIN_FILE_ID=t3.FILE_ID AND t2.LAST_TRANSACTION_DATE=t3.TRANSACTION_DATE
WHERE
t1.FILE_CODE in (SELECT receipt_number FROM my_sas_table)
ORDER BY
t1.FILE_CODE;
QUIT;

PROC Print data=work.temp2;
run;

[vtguy22]

Figured it out. I broke all of my joins into unique CREATE TABLEs. Not elegant, but it works and is fast..

[s_lassen · Accepted solution]

@vtguy22: There is another way to get stuff into an IN clause in Oracle - put the unique values (in single quotes) in a macro variable:

Proc sql;
  select distinct cats("'",tranwrd(receipt_number,"'","''"),"'") into :receipts separated by ','
  from my_sas_table;
quit;
  

The TRANWRD() doubles single quotes already in the variable, so that they get quoted correctly.

You should now be able to do your Oracle query as passthrough, using

where t1.FILE_CODE in(&receipts)

instead of referencing the SAS table which causes SAS to issue dynamic WHERE clauses, which is very inefficient.

There is a limit to how much you can squeeze into a WHERE clause this way, I think the current limit is 1000 items in an Oracle IN clause, and the limit for the length of SAS macro variables is 64K. But as long as you are within those limits, you should be fine.

[vtguy22]

Thank you so much, that is beautiful!