how to subset observations from a group ?

annapurna — Sat, 13 Dec 2014 03:18:23 GMT

Hi everyone

here is the data after grouping my sample data by subject variable to find counts.

proc sql;

select subject,marks,count(*) as count

from marks

group by subject,marks;

quit;

subject marks count

maths 70 3

maths 85 10

maths 90 5

physics 55 3

physics 60 7

physics 80 2

English 75 9

English 85 5

i now want to create a table with only those observations with max(count) from each subject,which should resemble like this

subject marks count

maths 85 10

physics 60 7

English 75 9

help me how to write a prog using proc sql to get this desired output (i dont want to use first. last. method from data step)

thanks in advance.

Re: how to subset observations from a group ?

stat_sas — Sat, 13 Dec 2014 04:37:39 GMT

proc sql;

select * from marks

group by subject

having count=max(count);

quit;

annapurna — Sat, 13 Dec 2014 10:48:16 GMT

thankyou stat@sas

its working