topic Re: Proc SQL with a macro in where statement to select on ID's in SAS Programming

Proc SQL with a macro in where statement to select on ID's

NickVe — Mon, 23 Dec 2019 10:29:48 GMT

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

The macro reads the input ID values from an input table: "input_table".
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;

Re: Proc SQL with a macro in where statement to select on ID's

LinusH — Mon, 23 Dec 2019 11:59:46 GMT

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

Take a look at DBKEY option.

Re: Proc SQL with a macro in where statement to select on ID's

novinosrin — Mon, 23 Dec 2019 12:52:44 GMT

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*/

Re: Proc SQL with a macro in where statement to select on ID's

NickVe — Mon, 23 Dec 2019 13:16:23 GMT

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

Re: Proc SQL with a macro in where statement to select on ID's

NickVe — Mon, 23 Dec 2019 13:16:50 GMT

Thanks for the tip, I will have a look at this!

Re: Proc SQL with a macro in where statement to select on ID's

PaigeMiller — Mon, 23 Dec 2019 13:33:07 GMT

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.