Delete observations in BY group on condition

[miss2223]

INPUT

ID          Paper       Grade          Sign up number
100000 English      B                   3006470
100001 English      B                  1305404
100001 History       A                   2914465
100001 Chemistry  D                  2144401
100002 English      A                   221023
100002 Maths        A                   432565

I want output to remove all records from ID 100001, as 100001 got D grade for Chemistry.

OUTPUT

ID          Paper       Grade          Sign up number
100000 English      B                   3006470
100002 English      A                   221023
100002 Maths        A                   432565

please help! I am using SAS EG. I tried to use Querybuilder to filter out grade D, but then it doesnt link to ID, hence the output still appear ID100001 with the grade for English and History. Ideally I would like to solve it with querybuilder, as I am not familiar with the programming code please!

Thanks in advance. 

[novinosrin · Accepted solution]

data have;
input ID    $      Paper   $    Grade     $     Signupnumber	;
cards;
100000 English      B                   3006470
100001 English      B                  1305404
100001 History       A                   2914465
100001 Chemistry  D                  2144401
100002 English      A                   221023
100002 Maths        A                   432565
;

proc sql;
create table want as
select *
from have
group by id
having max(grade='D')=0;
quit;

[singhsahab]

Hello Sir,

Can you please explain the working of below statement . 

having max(grade='D')=0

Thanks... 

[novinosrin]

I'm lazy hence I use proc sql a lot knowing it is lot slower than datastep for most cases. OK, furthermore i am merely taking advantage of boolean operation for by groups that has grade D will have 1 and the others will have 0. The max(1,0) is of course 1 for true cases were grade="D" and our interest are 0s where grade=D is false.

Try solving in datastep using merge, DOW, or self imposed interleave, or hash etc. You will see sql being slower but thats ok for me

---

@singhsahab wrote:

Hello Sir,

Can you please explain the working of below statement . 

having max(grade='D')=0

Thanks... 

---

[novinosrin]

proc sort data=have out=_have;
by id;
run;

data want;
merge _have(in=a) _have(in=b where=(grade='D'));
by id;
if a and not b;
run;

[singhsahab]

thank you 🙂