Hi,
I am trying the below but it is not working. I am not getting any data. please suggest.
data child_id;
infile "$inbound_path/program_child_id.txt" dlm=",";
input id :$11. @@;
run;
proc sql;
select * into :child separated by ','
from child_id;
quit;
create table x as select * from connection to oracle
(select a.*, b.* from table1 a,
table2 b
where a.program_id in (&program)
and trim(a.program_child_id) in ('&child')
and a.program_id = b.program_id
and a.program_child_id = b.program_child_id
);
Hi,
Macro variables need to be enclosed within double quotes. So your statement;
select * into :child separated by '","'
Note the single then double quotes and doubel then single. Then in your program:
and trim(a.program_child_id) in ("&child")
Thus it resolves to: in ("abc","def","efg").
Same for program:
where a.program_id in ("&program")
So your code needs to resolve to SQL compliant results.
This didnt work.
Sorry, you will have to be more specific, what didn't work, what errors/warnings etc.
Actually it failed.
ERROR: ORACLE prepare error: ORA-00904: "&child": invalid identifier.
Thank you. It is working after adding this-
(%unquote(%str(%'&child%')))
in where statement.
where findw("&program",strip(a.program),',')
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.