BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vtguy22
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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

View solution in original post

6 REPLIES 6
Sven111
Pyrite | Level 9

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
Super User

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

vtguy22
Calcite | Level 5

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
Calcite | Level 5
Figured it out. I broke all of my joins into unique CREATE TABLEs. Not elegant, but it works and is fast..
s_lassen
Meteorite | Level 14

@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
Calcite | Level 5

Thank you so much, that is beautiful!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2314 views
  • 1 like
  • 4 in conversation