Fetch Oracle Data by joining SAS table

[cheema11]

Hi All,

I have 3,000 customers in SAS table and I want to fetch data from Oracle for those customers. there are billions of records in Oracle table. I am using Oracle Lib and joining sas data with it, but the solution is not efficient, takes ages to run.

Please note that I don't have rights to create temp table in Oracle so cannot ingest 3,000 customers in oracle table.

The sample code is 

libname SASTABLE

libname ORATABLE

proc sql;

select A.* from oratable as a inner join sastable as b on a.cust_ID=b.cust_ID;

quit;

[SASKiwi · Accepted solution]

With only 3,000 customers, building a WHERE IN list using a macro variable should speed things up:

data Cust_List;
input CustID $;
datalines;
12345678
87654321
12344321
;
run;

proc sql noprint;
select quote(CustID)
into :Cust_List separated by ','
from Cust_List
;
quit;

%put Cust_List = &Cust_List;

libname oralib noprompt = '< Put Oracle connection string here>';

proc sql;
  create table Customers as
  select *
  from ORALIB.Customers
  where CustID in (&Cust_List)
  ;
quit; 

[cheema11]

Thanks @SASKiwi. Just one more query.
I am getting below error while creating the macro variable.

"The length of the value of the macro variable CUST_LIST (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters."
any idea how to fix this? One way is to go by 1000 customers separately, any thing more efficient?

[SASKiwi]

@cheema11  - Is your list longer than 3,000 customers then? How long is each cust ID? Please ensure you are not including blanks - quote(strip(CustID)). If the length is still over 65K, then splitting it into multiple macro variables is easy.

[Sajid01]

Your process is inefficient by design so don't expect efficiency.
Work with database teams to discuss ways and means to upload data to Oracle database .