Join Dataset from SAS Work library to Oracle Pass Through

[kiranv_]

I am confused now. what exactly you are trying to achieve. 

[Peter_B]

Sorry, I meant a working example of some code but it's not going to be possible as a Pass Through would be required. Thanks, I'll give this a try.

[SuryaKiran]

First thing is you need to understand how pass-through works. Pass-Through queries are used to send DBMS specific statements and retrieve DBMS data. You need to specify DBMS SQL syntax instead of SAS SQL or functions or SAS data. There is now way that you can use your SAS dataset in Pass-Through.

Your method to approach depends on what your trying to achieve.

1) Create a table in DBMS using temp table in SAS (Table in WORK Library) and DBMS table.

If this is your goal then the best approach is send the SAS temp table to DBMS temp and then do joins( @SASKiwi and @kiranv_ already gave you approach).

2) Create a table in SAS using SAS temp table and oracle table (not very large oracle table).

This will be a heterogeneous join, SAS will bring the records from DBMS to SAS environment and then do joins in SAS. This approach will be feasible if your oracle table is not very large ( fetch records are less).

options sastrace=',,,d' sastraceloc=saslog;  Using this option you can see how the sql is passed to DBMS and how many records are fetching.

LIBNAME ORC oracle user= password= path= ;
options sastrace=',,,d' sastraceloc=saslog;
proc sql;
create table sas_Table as 
select a.* 
	from orc.oracle_Table a
	inner join work.sas_temp b
	on (a.id=b.id);
quit;

3) If your oracle table is very large and want to create a table in SAS.

Follow the first approach