Passing sas character variables into oracle query

[archana]

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

);

[RW9]

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.

[RW9]

Also note, this is a duplicate of this thread:

[archana]

This didnt work.

[RW9]

Sorry, you will have to be more specific, what didn't work, what errors/warnings etc.

[archana]

Actually it failed.

ERROR: ORACLE prepare error: ORA-00904: "&child": invalid identifier.

[archana]

Thank you. It is working after adding this-

(%unquote(%str(%'&child%')))

in where statement.

[Ksharp]

where findw("&program",strip(a.program),',')