topic Re: Filter records based on conditions(Proc SQL) in SAS Programming

Filter records based on conditions(Proc SQL)

sahoositaram555 — Wed, 20 Jan 2021 13:13:19 GMT

Hi,

I've records in A, B , C datasets and all have a common column named Subject_ID(multiple recors per subject_id) . I want to filter the Subject_IDs which are in A but not in B&C(together).

below code doesn't give any syntax errors but not giving correct results also, shall require help.

proc sql;
create table filter as select distinct subject_ID from A where subject_ID not in
(select distinct subject_ID from B where subject_ID not in (select distinct subject_ID from C));
quit;

Re: Filter records based on conditions(Proc SQL)

PeterClemmensen — Wed, 20 Jan 2021 13:18:07 GMT

See if you can use this as a template. You can also do this with inner joins.

data a;
input subject_ID;
datalines;
1
2
3
4
5
;

data b;
input subject_ID;
datalines;
1
3
5
;

data c;
input subject_ID;
datalines;
1
2
3
;

proc sql;
   create table want as
   select * from a where subject_ID not in (select subject_ID from b)
                     and subject_ID not in (select subject_ID from c);
quit;

Re: Filter records based on conditions(Proc SQL)

Ksharp — Wed, 20 Jan 2021 13:38:17 GMT

data a;
input subject_ID;
datalines;
1
2
3
4
5
;

data b;
input subject_ID;
datalines;
1
3
5
;

data c;
input subject_ID;
datalines;
1
2
3
;

proc sql;
   create table want as
   select subject_ID from a 
   except
   select subject_ID from b
   except
   select subject_ID from c 
;
quit;