topic Re: Delete observations in BY group on condition in SAS Programming

Delete observations in BY group on condition

miss2223 — Sat, 24 Nov 2018 04:33:29 GMT

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.

Re: Delete observations in BY group on condition

novinosrin — Sat, 24 Nov 2018 04:51:52 GMT


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;

Re: Delete observations in BY group on condition

singhsahab — Sat, 24 Nov 2018 04:58:35 GMT

Hello Sir,

Can you please explain the working of below statement .

having max(grade='D')=0

Thanks...

Re: Delete observations in BY group on condition

novinosrin — Sat, 24 Nov 2018 05:05:34 GMT

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...

Re: Delete observations in BY group on condition

novinosrin — Sat, 24 Nov 2018 05:10:37 GMT


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;

Re: Delete observations in BY group on condition

singhsahab — Sat, 24 Nov 2018 17:18:42 GMT

thank you 🙂