BookmarkSubscribeRSS Feed
Apprentice
Calcite | Level 5

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;

 

 

 

 

5 REPLIES 5
Apprentice
Calcite | Level 5

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
PROC Star

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
Opal | Level 21

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
Super User

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
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 921 views
  • 0 likes
  • 5 in conversation