topic Re: passing variable into oracle query in SAS Programming

passing variable into oracle query

archana — Fri, 13 Mar 2015 13:52:38 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 variable into oracle query

LinusH — Fri, 13 Mar 2015 13:55:53 GMT

The log?

Can't see &program defined.

Skip the macro variables and test DBKEY= and implicit SQL pass-thru instead.

Re: passing variable into oracle query

archana — Fri, 13 Mar 2015 14:00:11 GMT

Program_id is defined in the same way. i wanted to enter the program id and child id in a file so that the query can pick them from file.

data program_id;

infile "$inbound_path/program_id.txt" dlm=",";

input id :8. @@;

run;

proc sql;

select * into :program separated by ','

from program_id;

quit;

Re: passing variable into oracle query

Tom — Fri, 13 Mar 2015 14:00:44 GMT

Macro triggers like & and % are not evaluated inside of single quotes.

Plus if you have a list of character literals in either SAS or Oracle they need to each be quoted separately.

You probably want code more like this:

data child_id;

infile "$inbound_path/program_child_id.txt" dlm=",";

length id $11 ;

input id @@;

run;

proc sql;

select catq('1s',id)

into :child separated by ','

from child_id

;

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

)

;

quit;

Re: passing variable into oracle query

archana — Fri, 13 Mar 2015 18:03:52 GMT

This didnt work.

Re: passing variable into oracle query

Tom — Fri, 13 Mar 2015 18:21:50 GMT

What did you actually try?

What was the actual value of the generated macro variable?

What was the actual code that ran?

Did you try it by hand first? Are you sure of the syntax that Oracle needs?

You can use macro variables and macro code to help you generate SAS code, but you need to know what actual code you want to generate first.

Re: passing variable into oracle query

PGStats — Fri, 13 Mar 2015 18:23:37 GMT

Tom spent some time answering your question. Try to spend some also at answering his efforts. How did "This didn't work'? Could you at least post your SAS log?

Re: passing variable into oracle query

archana — Fri, 13 Mar 2015 20:31:14 GMT

Thank you. it worked after adding

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

in the where statement.

Re: passing variable into oracle query

Tom — Fri, 13 Mar 2015 20:49:14 GMT

Sounds like you are trying to test for a single value rather than a list of values. Why are you using IN operator instead of = ?

Try this and see what you get in the LOG as value of NAMELIST macro variable.

proc sql noprint ;

select catq('1as',name) into :namelist separated by ','

from sashelp.class

;

quit;

%put &namelist;