I tried to filter data where they are on a list by using Data step in SAS
proc sql;
create table id_list as
select distinct id from customer;
quit;
data test;
set fulldata;
where id in id_list;
run;
It doesnt work. However, if I use "where id in (1,2,3)" it works. Could anyone please help me with where in a list of data ? Thanks
CodePudding user response:
You need to use a macro variable to save and reference your id list. You cannot reference a separate table in a where statement within the data step.
proc sql noprint;
select distinct id
into :id_list separated by ','
from customer
;
quit;
&id_list
will resolve to 1,2,3
if your distinct customer IDs are 1
, 2
, and 3
.
data test;
set fulldata;
where id in(&id_list);
run;
CodePudding user response:
SQL is easiest here directly, using a subquery in the IN filter.
proc sql;
create table test as
select *
from fulldata
where id in
(select distinct id from customer);
quit;
CodePudding user response:
If your fulldata is sorted or indexed by id then you can use a MERGE.
This approach is useful when the list is very large and could exceed 64K characters when placed in a macro variable.
proc sort data=customer(keep=id) out=list nodupkey;
by id;
data want;
merge fulldata(in=full) list(in=list);
by id;
if full and list;
run;