DATA Step, Macro, Functions and more

passing variable into oracle query

Reply
Contributor
Posts: 31

passing variable 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
Posts: 5,424

Re: passing variable into oracle query

The log?

Can't see &program defined.

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

Data never sleeps
Contributor
Posts: 31

Re: passing variable into oracle query

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 Smiley Tonguerogram separated by ','

from program_id;

quit;

Super User
Super User
Posts: 7,039

Re: passing variable into oracle query

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;

Contributor
Posts: 31

Re: passing variable into oracle query

This didnt work.

Super User
Super User
Posts: 7,039

Re: passing variable into oracle query

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.

Contributor
Posts: 31

Re: passing variable into oracle query

Thank you. it worked after adding

 

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

in the where statement.

Super User
Super User
Posts: 7,039

Re: passing variable into oracle query

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;

Respected Advisor
Posts: 4,919

Re: passing variable into oracle query

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?

PG
Ask a Question
Discussion stats
  • 8 replies
  • 691 views
  • 0 likes
  • 4 in conversation