This will only work for a limited number of items, as both the size of a macro variable (64K) and the size of a SQL statement have limits.
Simple proof of concept:
data names;
input name $;
datalines;
Alfred
William
;
proc sql noprint;
select quote(name) into :list separated by ","
from names;
quit;
proc sql;
select * from sashelp.class
where name in (&list.);
quit;
Alternatively, you can write the SQL code to a file (where you can have a lot more items in the IN list) and then %include that:
filename xx temp;
data _null_;
file xx;
set names end=done;
length line $100;
if _n_ = 1
then line = cats('proc sql; select * from sashelp.class where name in ("',name,'"');
else line = cats(',"',name,'"');
put line;
if done then put ");quit;";
run;
%include xx /source2;
filename xx clear;
This will allow for a much longer list, as the size limit for SQL statements will be much higher than that for macro variables. Your code in the include should of course be tailored for explicit pass-through.
... View more