topic Re: Passing sas character variables into oracle query in SAS Data Management

Passing sas character variables into oracle query

archana — Fri, 13 Mar 2015 13:51:33 GMT

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

);

Re: Passing sas character variables into oracle query

RW9 — Fri, 13 Mar 2015 14:14:32 GMT

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.

Re: Passing sas character variables into oracle query

RW9 — Fri, 13 Mar 2015 14:18:15 GMT

Also note, this is a duplicate of this thread:

Re: Passing sas character variables into oracle query

Ksharp — Fri, 13 Mar 2015 14:39:49 GMT

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

Re: Passing sas character variables into oracle query

archana — Fri, 13 Mar 2015 16:42:33 GMT

This didnt work.

Re: Passing sas character variables into oracle query

RW9 — Fri, 13 Mar 2015 17:20:47 GMT

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

Re: Passing sas character variables into oracle query

archana — Fri, 13 Mar 2015 18:09:13 GMT

Actually it failed.

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

Re: Passing sas character variables into oracle query

archana — Fri, 13 Mar 2015 20:25:24 GMT

Thank you. It is working after adding this-

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

in where statement.