Delete matched observations

[dapenDaniel]

Hello. I have two data sets. In the Dataset A, an ID has multiple observations. Dataset B is a list of ID that I need to delete from Dataset A. The following is an example.

Dataset A                                                          Dataset B

ID              Observations                                         ID

aa                     a11                                                  aa

aa                     a12                                                  bb

aa                     a13

bb                     b12

bb                     b22

cc                     c45

cc                     c56

I want to delete the observations which have the same ID with dataset B from Dataset A. What code do I need? Thanks.

[PGStats]

proc sql;
delete from A
where id in (select id from B);
quit;

[novinosrin]

Don't pay much attention to this. I'm trying to learn indexes, that's all

data a;
input id;
cards;
1
2
2
3
3
3
;

data b;
input id;
cards;
3
;


proc sql;
create index id on a (id);
quit;

data a; 
set b; 
do until (_iorc_=%sysrc(_dsenom));
modify a key=id; 
select (_iorc_); 
when(%sysrc(_sok)) do; /* A match was found, update master */
remove;
end;
when (%sysrc(_dsenom)) do; /* No match was found */
_error_ = 0;
end; 
otherwise do; 
length errormessage $200.; 
errormessage = iorcmsg();
put "ATTENTION: unknown error condition: " errormessage;
end;
end;
end;
run;

[Reeza]

proc sql;
create table want as
select *
from tableA
where ID not in (select * from tableB);
quit;

---

@dapenDaniel wrote:

Hello. I have two data sets. In the Dataset A, an ID has multiple observations. Dataset B is a list of ID that I need to delete from Dataset A. The following is an example.

Dataset A                                                          Dataset B

ID              Observations                                         ID

aa                     a11                                                  aa

aa                     a12                                                  bb

aa                     a13

bb                     b12

bb                     b22

cc                     c45

cc                     c56

I want to delete the observations which have the same ID with dataset B from Dataset A. What code do I need? Thanks.

---

[Jagadishkatam · Accepted solution]

Alternatively in a data step by merge statement

data want;
merge A(in=a) B(in=b);
by id;
if a and not b;
run;