How do I delete multiple observations/rows based on one instance of a variable?

[asgee]

Hi all, 

Somewhat new to repeated measures but I'm working on a dataset that has repeated data. Below is a sample: 

ID	Visit	Label
AAA	1	XY
AAA	2	Z
AAA	3	.
BBB	1	XY
BBB	2	XY
CCC	1	Z
DDD	1	.
DDD	2	.
DDD	3	XY
EEE	1	Z
EEE	2	.
FFF	1	XY
GGG	1	Z
HHH	1	.
HHH	2	Z
HHH	3	XY

I want to delete any IDs where a label of XY appears at least once across any visit. The code I have tried below only seems to delete the exact rows that has an XY label, and not every row that belongs to that ID:

data want;
set have;
by id;
if label = "XY" then delete;
run; 

Essentially, what I'd want to do is produce a table below that removes all other rows of that ID:

ID	Visit	Label
CCC	1	Z
EEE	1	Z
EEE	2	.
GGG	1	Z

Any help would be much appreciated! 

Thanks

[novinosrin · Accepted solution]

data have;
 input 
ID $

Visit $

Label $;
cards;
AAA

1

XY

AAA

2

Z

AAA

3

.

BBB

1

XY

BBB

2

XY

CCC

1

Z

DDD

1

.

DDD

2

.

DDD

3

XY

EEE

1

Z

EEE

2

.

FFF

1

XY

GGG

1

Z

HHH

1

.

HHH

2

Z

HHH

3

XY
;

proc sql;
 create table want as
 select *
 from have
 where id not in (select id from have where label="XY");
quit;

[asgee]

Hi @novinosrin ! Thanks so much for helping me out. The code works perfectly! I definitely need to look into learning more about proc sql.

[RichardDeVen]

A SQL DELETE statement will remove rows from the data set without rewriting the whole table.

proc sql undo_policy=none;
  delete from have
  where ID in 
    (select ID from have where label='XY')
  ;
quit;