How to only exclude ID's with subsequent unwanted observations

[shasank]

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.

[Astounding · Accepted solution]

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.

[novinosrin]

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;

[shasank]

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