Join Dataset from SAS Work library to Oracle Pass Through

[Peter_B]

Hi,

I want to use the results of a query in my EG Project, (Version 7.1)  which goes to the WORK library, in an Oracle Pass Through but the Pass through cannot 'see' the WORK Dataset.

How can I get both together in one query?

Thanks

[Reeza]

You can't, you'll need to move them to the same location. 

Would SQL, not pass through work?

Depending on the type of join, you could also create the rules as macro variables and pass them to the query that way. This useful in a case where you want to select a subset and the subset list is local for example. 

[Peter_B]

Thanks Reeza,

I'll do whatever works, what's the simplest way, a Macro?

Could you help with how I would do this?

Thanks

[Reeza]

Note my comment, 'depends on the join'. You need to provide more details of what you're trying to accomplish first. 

[Peter_B]

Ok, I'm creating a table of ID Numbers in the WORK Library.

The Pass Through query connects to an Oracle Data Warehouse with large tables.

I want to use the WORK.IDs as a Subquery in the Pass Through, something like below;

Connect to Oracle .....etc as connection string;

Select * 

from Oracle.Table

Where ID in

(
Select distinct ID form WORK.ID
)

Disconnect from Oracle;
;quit;

Hope this is clearer. Thanks

[Peter_B]

I would have thought that this problem had come up before.

So how do people overcome this?

I'd really like to know what is possible to do this OR is it impossible?

Thanks  

[SASKiwi]

It is absolutely doable. You need to upload your SAS WORK table to Oracle as a temporary table. Then you join to it in a pass through query. Something like this should work:

libname Oraload Oracle << put Oracle connection details here >> 
   connection = global dbmstemp = yes insertbuff = 10000;

data Oraload.MyTempTable;
  set WORK.MySASTable;
run;

proc sql;
  connect using Oraload;
  create table MyMatchedTable as 
 select * 
  from connection to Oraload
  (select A.*

 FROM MyOracleTable A
 inner join MyTempTable B
 on A.ID = B.ID
);
quit; 

[Peter_B]

Thanks!

I'll try it out.

[OscarBoots2]

Thanks SASKiwi,

I've run this code:

Libname line was successful;

NOTE: Libref ORALOAD was successfully assigned as follows:

LIBNAME oraload ORACLE user=XXXXXXXX pw=XXXXXXX
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = XXXXXXXXXXXXXX.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=XXXXXXXX)))";

connection = global dbmstemp = yes insertbuff = 10000;

data oraload.mytemptable;
set WORK.CUST_IDS;
RUN; 

I got the below error at the connection line;

29         connection = global dbmstemp = yes insertbuff = 10000;
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Is there something needed in the CONNECTION statement?

Thanks

[SASKiwi]

The LIBNAME statement ends with a semicolon like all SAS statements. You have an extra semicolon where there shouldn't be:

LIBNAME oraload ORACLE user=XXXXXXXX pw=XXXXXXX
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = XXXXXXXXXXXXXX.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=XXXXXXXX)))"
connection = global dbmstemp = yes insertbuff = 10000;

[OscarBoots2]

Thanks, it ran but I then got the below privileges error.

I'll see about getting my access changed, but in the meantime, is there another way to do this?

Thanks

Spoiler

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-01031: insufficient privileges..

[SASKiwi]

Unfortunately not. If your Oracle account does not allow the creation of temporary tables then the only option is to get the account permissions changed.

[Reeza]

It's possible, and you'll get better answers if you include more details in your question to start off with. 

If you're using SQL pass through use the macro option I mentioned.  

So from your work table create a macro variable like this:

proc sql noprint;

select quote(name, "'") Into :name_list separated by ", "
from sashelp.class
where age in (12, 13);

quit;

Then you can add that into your query, assuming you know how your SQL query is written already, just add to the WHERE statement. 

options mprint symbolgen;

proc sql;

create table want as
select *
from sashelp.class
where name in (&ame_list);

quit;

Obviously this isn't a SQL pass through, but an example that you can run to see how it works. 

[kiranv_]

Either move SAS table to Oracle and then do joins  or subquery. below is example of join. you can apply this logic to your query

as shown in next two steps

proc sql;

create table oratable.tablename as

select * from sastable.tablename;

quit;

then use the above table in your query

proc sql; connect to oracle (user=&myid orapw=&mypwd path="&mydb");

execute ( use your logic here) by oracle;

disconnect from oracle; quit;

if above thing is not possible you have to do something like this.

libname oratable oracle user=user password=password path=path;

proc sql;

create table oratable.tablename

as select * from sastable.tablename

left join oratable.tablename on yourcolumns ;

quit;

[Peter_B]

Thanks kiranv,

Do you have a working example of this maybe using Dual?

Thanks