SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Passing sas character variables into oracle query

Reply
Contributor
Posts: 31

Passing sas character variables into oracle query

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

);

Super User
Super User
Posts: 7,392

Re: Passing sas character variables into oracle query

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.

Super User
Super User
Posts: 7,392

Re: Passing sas character variables into oracle query

Also note, this is a duplicate of this thread:

Contributor
Posts: 31

Re: Passing sas character variables into oracle query


This didnt work.

Super User
Super User
Posts: 7,392

Re: Passing sas character variables into oracle query

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

Contributor
Posts: 31

Re: Passing sas character variables into oracle query

Actually it failed.

 

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

Contributor
Posts: 31

Re: Passing sas character variables into oracle query

Thank you. It is working after adding this-

 

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

in where statement.

Super User
Posts: 9,671

Re: Passing sas character variables into oracle query

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


Ask a Question
Discussion stats
  • 7 replies
  • 481 views
  • 0 likes
  • 3 in conversation