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