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;
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;
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.