BookmarkSubscribeRSS Feed
Peter_B
Calcite | Level 5

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

 

 

17 REPLIES 17
Reeza
Super User

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

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

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

Peter_B
Calcite | Level 5

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

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
PROC Star

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

Thanks!

 

I'll try it out.

OscarBoots2
Calcite | Level 5

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
PROC Star

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

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
PROC Star

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

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_
Rhodochrosite | Level 12

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

Thanks kiranv,

 

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

 

Thanks

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 17 replies
  • 5302 views
  • 0 likes
  • 6 in conversation