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

Question

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;

Answer 1

The QUOTE function allows a second argument that specifies the quoting character. The value being quoted should also be TRIM'd.

Change your SQL to directly populate the macro variable value as a complete comma separated list of single quoted values.

Example:

proc sql noprint;
  select 
    quote(trim(name),"'") into :idlist separated by ',' 
  from 
    sashelp.class
  ;
quit;

%put &=idlist;
---------- log ----------
IDLIST='Alfred','Alice','Barbara','Carol','Henry','James','Jane',
'Janet','Jeffrey','John','Joyce','Judy','Louise','Mary','Philip','Robert',
'Ronald','Thomas','William'

The query where clause would then be simpler:

…
WHERE ID in ( &id_list )
…