topic Re: Use the results of a Pass Through query in another Pass Through query in SAS Enterprise Guide

Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 06:08:36 GMT

Hi Forum,

Using SAS EG 7.1 with Windows 7.

I have 2 large tables, one with 100,000s of records & one with millions of records that I access via an Oracle Pass Through query.

To keep the time taken to run down, I've firstly created a Pass Through that extracts the common data between the 2 tables reducing the data to 60K uniq IDs.

I now want to use that data as a join in a new Pass Through so I can get some more fields from these large tables.

The plan is that the tables now only need to check 60K of records instead of the entire table.

Unfortunately, a subquery still takes too long to run & when I try to add the extracted dataset in the Pass Through, it's not recognised

I wonder if the 60K of Unique IDs can be saved somewhere (Temp table?) so It will be usable in the second Pass Through?

Any suggestions welcome.

Thanks

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 06:34:01 GMT

This may help explain my issue;

I had a thought that if a temp table isn't an option, maybe an export to a new location may be an answer?

Just thinking of any possibilities.

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxx pw=xxxxxxxx);

create table WORK.xxxxxxxxx as select * from connection to ora
(
select distinct
A.ID

from QUERY A
LEFT JOIN QUERY_NEXT B
ON A.ID = B.ID

/*-----------------------------------------------*/

/*Is it possible to add code here to export the data back to another location?*/

/*--------------------------------------------------*/

);
disconnect from ora;
quit;

Re: Use the results of a Pass Through query in another Pass Through query

kiranv_ — Wed, 07 Mar 2018 06:38:49 GMT

yes this done easily. below is sample code for teradata but in oracle there is global temporary table which you can use. below code creates 2 temporary tables and then creates final table. please look into the paper given below. even though it is mostly for teradata, can be easily implied to oracle. http://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-57_Final_PDF.pdf

proc sql;
connect to teradata (server=myserver user=myuserid pw=mypass connection = global);
/* 1st volatile table created*/
execute(create volatile table staging_customer as
select * from edwwrkuser.Cusomter table
where create_dt between ‘2017-01-01’ and ‘2017-01-31’ )
with data primary index(cust_id)on commit preserve rows) by teradata;
execute(commit work) by teradata;
/* second Teradata table creation is not shown*/
/* final Teradata table to be created*/
execute(create volatile table Final_cust_txn
select a.* , b.txn_id from staging_customer a
inner join
staging_txn_tbl
on a.cust_id =b.cust_id)
with data no primary index on commit preserve rows)by teradata;
execute(commit work) by Teradata;
disconnect from teradata;
quit;

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 06:41:28 GMT

Thanks kiranv,

That's the sort of solution I'm after.

Let me give it a try.

Thanks

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 07:14:21 GMT

Hi kiranv,

Are you able to use my posted code to show me the correct Oracle syntax for this or suggest an article I could check?

I'm having some trouble blending the two together.

No worries if you're a Teradata user, I am too & I'm trying to get this right nit having used Teradata for a while.

Thanks

Re: Use the results of a Pass Through query in another Pass Through query

kiranv_ — Wed, 07 Mar 2018 13:59:01 GMT

can do create final tables in multiple steps in oracle by using global temporary table and then can access those tables outside of connect by using libname method. I have not tested any of this and it has been while I did something like this in oracle. but you will get the idea. I know @SuryaKiran is well versed with oracle pass through. l would like to hear what he thinks on this and what would be his suggestion.

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxx pw=xxxxxxxx connection =global);

excute( create global temporary table xyz as 
select distinct
A.ID

from QUERY A
LEFT JOIN QUERY_NEXT B
ON A.ID = B.ID) on commit preserve rows;
execute(commit) by oracle;

/*Is it possible to add code here to export the data back to another location?*/
excute( create global temporary table hello as 
select * from xyz
where name ='whatever') on commit preserve rows;
execute(commit) by oracle;



/*--------------------------------------------------*/

);
disconnect from ora;
quit;




/* you can use this temporary table by libname and create your sas table */


libname oradb oracle user=scott pw=tiger path=lupin connection=global dbtemp = yes;

proc sql;
create table work.table_you_want as select * from oradb.hello ;
quit;

Re: Use the results of a Pass Through query in another Pass Through query

Patrick — Wed, 07 Mar 2018 14:13:31 GMT

You can always combine queries as below. That's certainly as fast as first running the inner query, storing it in some table and then use this table for the next query.

Look at the query bit in brackets as an implicit view.

create table want as
  select 
    outer.*
    table3 outer
    left join
      (
        your current query joining two tables
      ) inner
    on outer.blah=inner.blah

Re: Use the results of a Pass Through query in another Pass Through query

SuryaKiran — Wed, 07 Mar 2018 15:26:28 GMT

It seems to me like, your trying to avoid Heterogeneous joins between SAS Table and Oracle tables. The best approach is load the SAS table(Just the Key variables you need) into permanent table in Oracle and then run pass-through for in-database query. Most of the environments will not allow user to create permanent tables in PROD, in this situation check whether you can create a GLOBAL TEMPORARY table for Oracle or MULTISET VOLATILE TABLE in Teradata. If yes, then load the Key variable values for 60K into temp table.

Note: Loading data into temp table is not as fast as loading data into permanent table.

libname ora oracle user= pw= path= connection=global DBMSTEMP=YES;
  proc sql;
  connect to oracle (user= pw= path= connection=global);
   execute (create global temporary table TEMP
       (ID number) on commit preserve rows) by oracle;
  quit;

proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET;
run;

Remember to keep CONNECTION=GLOBAL and ON COMMIT PRESERVE ROWS

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 20:44:07 GMT

Thanks Patrick,

I have tried a subquery but it is still far to slow to help in this situation.

Cheers

Re: Use the results of a Pass Through query in another Pass Through query

Patrick — Wed, 07 Mar 2018 21:13:50 GMT

Reading your original post again: You're first joining the two tables to get the common keys and then you want to join again to the one big table to get additional columns. That won't speed up things.

Just join the two tables and get all the columns you want in a single select statement. If the join is too slow then reformulate your join condition; ideally in a way that it can use existing indeces and if this doesn't exist then by trying to avoid OR conditions or and by using the comparisons which will be rarely true first in your condition (so the rest doesn't need testing anymore).

If using pass-through look also into Oracle hints allowing you to tell Oracle to execute the query in parallel.

And last but not least: Make sure you set option READBUFF to a bigger value than installation default because may-be the time consuming part is not your query on the Oracle side but the data transfer from Oracle to SAS.

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 21:27:16 GMT

Thanks to all for your input so far.

Suryakiran, I've changed your code to do what I think I need to do to get the connection right, can you advise if it's correct or not.

Note, I have HOST & PATH in my connection criteria, how do I adapt it to a Global Temp criteria.

Thanks

libname ora oracle user=xxxxxxxx pw=xxxxxxx path= ?
(HOST = XXXXXXXXXXXXXXX.com) (PORT = 1234)
connection=global DBMSTEMP=YES;

proc sql;
connect to oracle (user=XXXXXXXX pw=XXXXXXXX path= ? 
connection=global);

   execute (create global temporary table TEMP
       (ID number) on commit preserve rows) by oracle;
  quit;

proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET;
run;

Re: Use the results of a Pass Through query in another Pass Through query

SuryaKiran — Wed, 07 Mar 2018 21:45:48 GMT

Your are using a complex type of connection instead of defining your connection entries in tnsnames.ora. Work with your DBA to set up the tnsnames.ora for connecting to oracle with less hassle. Anyways if you prefer this way then check this LIBNAME

libname oralib1 oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = ))
(CONNECT_DATA = (SERVICE_NAME=)))" user=myuser password=mypasswd connection=global dbmstemp=yes;

For more information take a look at this topic: A quick guide to connecting to Oracle from SAS by @JBailey

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 22:04:53 GMT

Thanks Suryakiran,

That worked or at least didn't error, so I assume I now have created a temp table because there's no visible result.

How can I now insert my records from a query into this table?

Thanks

Re: Use the results of a Pass Through query in another Pass Through query

SuryaKiran — Wed, 07 Mar 2018 22:18:39 GMT

/* This Query will only create an empty table */
proc sql;
connect to oracle (user=XXXXXXXX pw=XXXXXXXX path= ? connection=global);
   execute (create global temporary table TEMP
       (ID number) on commit preserve rows) by oracle;
  quit;
/* Insert records into temp table using PROC APPEND */
proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET;
run;

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 22:26:06 GMT

Thanks, should I be adding a select statement after 'data=' in order to get results?

Re: Use the results of a Pass Through query in another Pass Through query

SuryaKiran — Wed, 07 Mar 2018 22:36:32 GMT

There is no such select statement for PROC APPEND. There are many ways to load data into Temp table one method is PROC APPEND. If you want to see the table then use the data set or run select query from PROC SQL.

data ora.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES);

set <sasdataset>;

run;

You need BL_DELETE_DATAFILE=YES to delete .dat, .ctl and .log files which are by default created when you use BULKLOAD.

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 22:44:49 GMT

Thanks so to see my temp table results, I should use the below?

libname oralib1 oracle path="(DESCRIPTION=(ADDRESS =
(PROTOCOL = TCP)(HOST =xxxxxxxxxxxx.com)(PORT =1234 ))
(CONNECT_DATA = (SERVICE_NAME=xxxxxx)))"
user=xxxxxxxx pw=xxxxxxxxx connection=global dbmstemp=yes;

/* This Query will only create an empty table */
proc sql;
connect to oracle (user=xxxxxx pw=xxxxxx connection=global);
   execute (create global temporary table TEMP
       (ID number) on commit preserve rows) by oracle;
  quit;

  /* Insert records into temp table using PROC APPEND */
proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET;
run;

data ora.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES);

set SAS_DATASET =
select distinct ID from mytable
;

run;

Re: Use the results of a Pass Through query in another Pass Through query

SuryaKiran — Wed, 07 Mar 2018 22:58:17 GMT

If your using SAS EG then if you Load data using Data step then the result will be in output window.

libname oralib1 oracle path="(DESCRIPTION=(ADDRESS =
(PROTOCOL = TCP)(HOST =xxxxxxxxxxxx.com)(PORT =1234 ))
(CONNECT_DATA = (SERVICE_NAME=xxxxxx)))"
user=xxxxxxxx pw=xxxxxxxxx connection=global dbmstemp=yes;

/* This Query will only create an empty table */
proc sql;
connect to oracle (user=xxxxxx pw=xxxxxx connection=global);
   execute (create global temporary table TEMP
       (ID number) on commit preserve rows) by oracle;
  quit;
/* You defined your libname as oralib1 not ORA */
 /* Insert records into temp table using Data Step */
data oralib1.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES);
set SAS_DATASET;
run;

Or just run this to confirm the records loaded.

proc sql;

select count(*) from oralib1.temp;

quit;

Re: Use the results of a Pass Through query in another Pass Through query

OscarBoots2 — Wed, 07 Mar 2018 23:02:59 GMT

Ok, I think I have it, could you give me a Data Step insert statement I could use for this?

Thanks

Re: Use the results of a Pass Through query in another Pass Through query

Patrick — Wed, 07 Mar 2018 23:24:03 GMT

@OscarBoots2

If your remaining problem is to select rows in a large Oracle table based on keys/ids in a small SAS table then consider also the DBMASTER dataset option.

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n0jg0sozl17mjyn1woelrrr76266.htm&docsetVersion=9.4&locale=en