topic Re: How to only exclude ID's with subsequent unwanted observations in SAS Data Management

How to only exclude ID's with subsequent unwanted observations

shasank — Tue, 21 Aug 2018 18:41:14 GMT

Hi Community,

I wanted your help in the following issue.

data dsn;
input ID $1. Seq $4. Group $5.;
cards;
1 01 BC
1 02 BC
2 00 LC
3 01 BC
3 02 LC
4 00 BC
5 01 BC
5 02 KC
5 03 BC
5 04 KC
;
run;

I want to delete all the ID's which have group other than BC. Not just the row but all the rows of that ID.

Thank you for your time and Help.

Re: How to only exclude ID's with subsequent unwanted observations

Astounding — Tue, 21 Aug 2018 18:51:32 GMT

There are a few possibilities This one assumes your data is sorted as indicated in your sample data:

data want;

merge have have (where=(group ne "BC") in=delete_these);

by id;

if delete_these then delete;

run;

There will be a message about a many-to-many merge, but the result will still be correct.

Re: How to only exclude ID's with subsequent unwanted observations

novinosrin — Tue, 21 Aug 2018 18:53:13 GMT

data dsn;
input ID $1. Seq $4. Group $5.;
cards;
1 01 BC
1 02 BC
2 00 LC
3 01 BC
3 02 LC
4 00 BC
5 01 BC
5 02 KC
5 03 BC
5 04 KC
;
run;

proc sql;
create table want as
select *
from dsn
group by id
having max(upcase(Group) ne 'BC')=0
order by id, seq;
quit;

Re: How to only exclude ID's with subsequent unwanted observations

shasank — Tue, 21 Aug 2018 20:32:16 GMT

Thank you for your response and time. This worked too.