using both SAS Dataset and Oracle table in pass though Proc Sql

[deleted_user]

Hi,

I have a perfect code to run in Proc SQL fetching data from Oracle and its a pass-through approach with many inner joints and conditions.

There is an existing dataset which has a single column of data, where I would like to include in the conditions in the joins in Proc Sql.

I found some options to do this by converting the sas dataset to oracle table and use it as a oracle table in the joins. But its just a waste of time to process few millions of records and also waste of space in the DB.

Is there any other way that I can directly use a SAS dataset in a Pass though Proc SQL which connects to Oracle and joins many table to form a sas dataset? So it will be like one of the tables used in joins and conditions.

Thanks,
Activa.

[Patrick]

Hi

If you want to join a Oracle table with a SAS table then you have either to load the SAS table into Oracle - or the Oracle table into SAS.


I've seen 2 efficient ways how to do this on the Oracle side:

1. You upload the SAS table into a Oracle temporary table

2. If the SAS table is not too big and it's only a key column: You store all the key values in a SAS macro variable and use this macro variable in the where clause on the Oracle side.

HTH
Patrick

[deleted_user]

Hi Patrick,

A key field in many oracle tables are in the SASDataset, and it having millions of records. Loading oracle to SAS takes more time than SAS to oracle with a single column. Either way the size and the time of load will affect performance and storage space.

I prefer use the SASDataset as one of the tables in the Oracle Joins in the Pass through PROC Sql.

Is there any way we can do it ? so performance is not affected.

Thanks,
Activa.

[Patrick]

Hi Activa

As you've decided to use explicit pass-through instead of a libname you will have to do everything "manually".

The code in the "connect block" is pure Oracle SQL and executed on the DB. If you want to use data from somewhere else (i.e. from a SAS table) then you first have to load this table into the DB to access it.

And yes: Uploading data will cost some time and affect performance. No way around it.

Storage space shouldn't be such an issue if you create a temporary table:
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...

From a performance perspective: If these are only keys then I don't expect the table to be too huge (in bites) and performance gains might be better achieved by tweaking the joins (i.e. so that indexes are used or only selected table partitions addressed).

HTH
Patrick