Filter records based on conditions(Proc SQL)

[sahoositaram555]

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;

[PeterClemmensen · Accepted solution]

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;

[Ksharp]

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;