Desktop productivity for business analysts and programmers

Join Dataset from SAS Work library to Oracle Pass Through

Reply
Occasional Contributor
Posts: 7

Join Dataset from SAS Work library to Oracle Pass Through

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

 

 

Super User
Posts: 22,843

Re: Join Dataset from SAS Work library to Oracle Pass Through

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. 

Occasional Contributor
Posts: 7

Re: Join Dataset from SAS Work library to Oracle Pass Through

Thanks Reeza,

 

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

 

Could you help with how I would do this?

 

Thanks

Super User
Posts: 22,843

Re: Join Dataset from SAS Work library to Oracle Pass Through

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

Occasional Contributor
Posts: 7

Re: Join Dataset from SAS Work library to Oracle Pass Through

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

 

Occasional Contributor
Posts: 7

Re: Join Dataset from SAS Work library to Oracle Pass Through

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  

Super User
Posts: 3,766

Re: Join Dataset from SAS Work library to Oracle Pass Through

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; 
Occasional Contributor
Posts: 7

Re: Join Dataset from SAS Work library to Oracle Pass Through

Thanks!

 

I'll try it out.

Contributor
Posts: 60

Re: Join Dataset from SAS Work library to Oracle Pass Through

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

Super User
Posts: 3,766

Re: Join Dataset from SAS Work library to Oracle Pass Through

Posted in reply to OscarBoots2

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;
Contributor
Posts: 60

Re: Join Dataset from SAS Work library to Oracle Pass Through

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..
Super User
Posts: 3,766

Re: Join Dataset from SAS Work library to Oracle Pass Through

Posted in reply to OscarBoots2

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.

Super User
Posts: 22,843

Re: Join Dataset from SAS Work library to Oracle Pass Through

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. 

PROC Star
Posts: 499

Re: Join Dataset from SAS Work library to Oracle Pass Through

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;

 

Occasional Contributor
Posts: 7

Re: Join Dataset from SAS Work library to Oracle Pass Through

Thanks kiranv,

 

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

 

Thanks

Ask a Question
Discussion stats
  • 17 replies
  • 257 views
  • 0 likes
  • 6 in conversation