passing variable 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

);

[LinusH]

The log?

Can't see &program defined.

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

[archana]

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;

[Tom]

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;

[archana]

This didnt work.

[Tom]

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.

[archana]

Thank you. it worked after adding

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

in the where statement.

[Tom]

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;

[PGStats]

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?