topic Re: Join Dataset from SAS Work library to Oracle Pass Through in SAS Enterprise Guide

Join Dataset from SAS Work library to Oracle Pass Through

Peter_B — Fri, 30 Mar 2018 01:37:48 GMT

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

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

Reeza — Fri, 30 Mar 2018 02:04:15 GMT

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.

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

Peter_B — Fri, 30 Mar 2018 02:06:38 GMT

Thanks Reeza,

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

Could you help with how I would do this?

Thanks

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

Reeza — Fri, 30 Mar 2018 02:09:05 GMT

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

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

Peter_B — Fri, 30 Mar 2018 02:16:24 GMT

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

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

Peter_B — Fri, 30 Mar 2018 03:08:32 GMT

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

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

kiranv_ — Fri, 30 Mar 2018 03:26:50 GMT

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;

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

Peter_B — Fri, 30 Mar 2018 03:29:14 GMT

Thanks kiranv,

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

Thanks

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

kiranv_ — Fri, 30 Mar 2018 03:34:53 GMT

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

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

SASKiwi — Fri, 30 Mar 2018 03:38:12 GMT

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;

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

Peter_B — Fri, 30 Mar 2018 03:41:07 GMT

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.

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

Peter_B — Fri, 30 Mar 2018 03:44:55 GMT

Thanks!

I'll try it out.

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

SuryaKiran — Fri, 30 Mar 2018 13:15:20 GMT

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

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

Reeza — Sat, 31 Mar 2018 01:44:35 GMT

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.

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

OscarBoots2 — Sun, 01 Apr 2018 03:46:22 GMT

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

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

SASKiwi — Sun, 01 Apr 2018 22:31:20 GMT

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;

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

OscarBoots2 — Mon, 02 Apr 2018 03:32:02 GMT

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

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..

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

SASKiwi — Mon, 02 Apr 2018 21:11:11 GMT

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.