BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NickVe
Obsidian | Level 7

I am currently trying to write a small SAS macro that does the following:

  1. The macro reads the input ID values from an input table: "input_table".
  2. The ID values are used to query an oracle database for the variable "TARGET".

The macro is shown below.

Whenever I run the macro, the filtering on ID does not seem to work and the proc sql return an empty table. I can not get my head around what might be going wrong, all help is welcome!

My current solution is using an inner join, which does the job. However, the SQL solution is strongly preferred for efficiency reasons.


QUESTION: Why is the Proc SQL not selecting records based on the list "id_list"?


%macro query_DB_from_table(input_table = , output_table = );
/* PART 1: Get IDs from the input table */
%local id_list;
proc sql noprint;
    select ID into: id_list separated by "' , '"
    from &input_table;
quit;
/* PART 2: Query the Oracle Database */
proc sql noprint;
    create table &output_table as
    select ID, TARGET
    from ORACLE_DB
    where ID in (%str(')%bquote(&id_list)%str('))
    order by ID;
quit;
%mend query_DB_from_table;
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

HI @NickVe  You could keep it much simpler using quote function . Example:

 

  select quote(ID) into: id_list separated by ' , '
    from &input_table;




where ID in (&id_list)  /*filter*/

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

It seem that you are trying to do something that SAS offers OOTB.

Take a look at DBKEY option.

Data never sleeps
NickVe
Obsidian | Level 7
Thanks for the tip, I will have a look at this!
novinosrin
Tourmaline | Level 20

HI @NickVe  You could keep it much simpler using quote function . Example:

 

  select quote(ID) into: id_list separated by ' , '
    from &input_table;




where ID in (&id_list)  /*filter*/
NickVe
Obsidian | Level 7

Thanks! This is indeed a much cleaner solution to the problem.

PaigeMiller
Diamond | Level 26

Good suggestions given above ... I would like to add an explanation as to why this macro didn't work as originally written.

 

First, you should always turn on options mprint; before you run any macro, this will show in the log the code that SAS generates, making the whole thing easier to debug.

 

You have a syntax error, you should be using 

 

%str(%')

twice, as otherwise the single quote is not properly resolved when SAS executes; the % sign indicating to the %STR() function that there is a single unmatched quote. 

 

Next you need the %unquote() function:

 

    where name in (%unquote(%str(%')%bquote(&id_list)%str(%')))

but the reasoning isn't particularly simple to explain. All I know is that when it seems like your code is properly generated in the log, and yet SAS reports errors, %unquote() often fixes the problem. 

 

The moral of the story is that creating macro strings with quotes and commas can be difficult, and the simpler methods (such as that provided by @novinosrin of creating a quoted string works much better and with many fewer complications, and so I highly recommend that you don't try to add your own quotes around the value of a macro variable unless nothing else seems to work.

--
Paige Miller

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 5 replies
  • 2279 views
  • 1 like
  • 4 in conversation