BookmarkSubscribeRSS Feed
archana
Fluorite | Level 6

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

);

8 REPLIES 8
LinusH
Tourmaline | Level 20

The log?

Can't see &program defined.

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

Data never sleeps
archana
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6

This didnt work.

Tom
Super User Tom
Super User

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
Fluorite | Level 6

Thank you. it worked after adding

 

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

in the where statement.

Tom
Super User Tom
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2934 views
  • 0 likes
  • 4 in conversation