Comparing SAS data with Oracle/Terradata/SQL data

[Siddhu]

I have a dataset in SAS with 1000 observations containing account numbers and I have another table in Oracle/Terradata/SQL with trillions of records(cannot be downloaded) containing the transaction details of those account numbers. Now, I want to get the transaction details of those 1000 account numbers using SAS programming. 

Note: The comparison of data must be done without bringing the data from other server to SAS.

Can anyone help?

If not possible to write the code, at least try filling me with logic

[Kurt_Bremser]

Upload your 1000 account numbers into a temporary table in the DBMS and use that for a join there (using explicit passthrough). Then download the results to SAS.

[Patrick]

@Siddhu 

Besides of what @Kurt_Bremser proposes a volume as low as a 1000 accounts would also allow to populate a SAS macro variable with these numbers and then use this macro variable in a where clause querying the Oracle table. The where clause will then execute within Oracle and you only transfer matching transaction to SAS.

Code like below should do this job.

proc sql noprint;
  select cats("'",account_no,"'") into :account_no_list separated by ','
  from SASTable
  ;
  create table matching_transactions as
    select *
    from OracleTable
    where account_no in (&account_no_list)
  ;
quit;

[Siddhu]

Just small doubt bro.

when we set a connection to Oracle using pass-through facility then the connection to SAS will be lost, then, will the macro variable be able to get executed with where statement when connected to oracle?

I know that macro variables are processed at compile time but just need clarity 

And, thanks so much for the response

[Patrick · Accepted solution]

The SAS macro variable will resolve on the SAS side. The syntax SAS will send to Oracle for execution will have the content of the macro variable in it (all account numbers in the where condition). 

Oracle accepts SQL code of up to 32KB so 1000 account numbers shouldn't be an issue.

You can use the SAS macro variable in both implicit and explicit pass-through code. It doesn't make a difference because as said - the macro variable gets resolved before SAS even sends any code to Oracle for execution.

[Siddhu]

Thanks so much. You are helpful