SAS DATA STEP WHERE CONDITION IN A LIST-CodePudding

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;