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

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

asgee — Sun, 18 Oct 2020 23:12:08 GMT

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

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

novinosrin — Sun, 18 Oct 2020 23:36:53 GMT

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;

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

asgee — Mon, 19 Oct 2020 00:08:20 GMT

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

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

RichardDeVen — Tue, 20 Oct 2020 19:21:34 GMT

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;