Can I pass a SAS dataset column as constraint to a SQL pass through query to Oracle?

[Apprentice]

Hello,

I am trying to use a legacy SQL Plus query as a step in my process where in I am trying to pass a column from my sas dataset as a constraint to the sql passthrough. This can make alot of legacy query work useful again.

Seeking advise if it is doable and how? I have trolled through the posts and haven't been able to find a plausable solution. I have in the past successfully passed single variable values as contraints to the sql passthrough queries.  

Many Thanks in advance.

Example:

sas dataset - work.test_ids

Passthrough query:

proc sql;

  connect to oracle (user=*** orapw=*** path=****);

     create table work.temp as select * from connection to oracle

         ( select * from oracle_schema.oracle_table where user_ids in (select distinct t1.test_users from work.test_ids t1));

   disconnect from oracle;

quit;

[Apprentice]

Hello,

I am trying to use a legacy SQL Plus query as a step in my process where in I am trying to pass a column from my sas dataset as a constraint to the sql passthrough. This can make alot of legacy query work useful again.

Seeking advise if it is doable and how? I have trolled through the posts and haven't been able to find a plausable solution. I have in the past successfully passed single variable values as contraints to the sql passthrough queries.  

Many Thanks in advance.

Example:

sas dataset - work.test_ids

Passthrough query:

proc sql;

  connect to oracle (user=*** orapw=*** path=****);

     create table work.temp as select * from connection to oracle

         ( select * from oracle_schema.oracle_table where user_ids in (select distinct t1.test_users from work.test_ids t1));

   disconnect from oracle;

quit;

[SASKiwi]

I've done this sort of thing previously by loading the SAS table to Oracle first as a temporary table. Then you can run your passthru query referencing the temporary table. In the example as you have written it the SAS table is not yet loaded into Oracle so the query will fail.

[Patrick]

As @SASKiwi writes you will first need to load your SAS table into Oracle for using it in an Oracle pass-through query.

Assuming "work.test_ids" is a rather small table what in your specific case also could work is to load the "test_id's" into a macro variable and use it as a string in your query.

proc sql noprint;
  select distinct cats("'",test_users,"'") into :test_users separated by ','
  from work.test_ids
  ;
quit;

proc sql;
  connect to oracle (user=*** orapw=*** path=****);
  create table work.temp as select * from connection to oracle
    ( select * from oracle_schema.oracle_table where user_ids in (&test_users));
  disconnect from oracle;
quit;

[Reeza]

Another option, depending on the size of your list, is to make a macro variable containing those values and use that in the query. 

Macro variables have a maximum length of 64k characters. 

[LinusH]

Take a look at DBKEY= data set option.
It does not only let you skip the upload of the SAS data set, it lets you write an implicit SQL pass through instead of an explicit. Will simplify your coding imo.