SAS Pass Through Query Interacting With a SAS Dataset

Suppose you have a list of customer account IDs in a SAS dataset and want to find all the customer records of these account IDs in an Oracle table. Since you can't join a SAS dataset with an Oracle table within a pass through query, you have to resort to SAS macro to pass the list into the query. Here is the SAS sample code which shows how to do this:


data acct_id;
input acct_id $;
datalines;
001
002
; run;

data _null_;
set acct_id end = last;
length acct_list $1000.;
retain acct_list;

if _n_ = 1 then acct_list = "'"||trim(acct_id)||"'";
else acct_list = trim(acct_list)||",'"||trim(acct_id)||"'";

/*export the list to a macro variable*/

if last then call symput("acct_list", acct_list);
run;

%put &acct_list;

proc sql;
connect to oracle (user=your_user_id password=your_password);
create table cust_data as
select * from connection to oracle
(
select *
from cust_db
where acct_id in (&acct_list.)
);
disconnect from oracle;
quit;