how to subset observations from a group ?

[annapurna]

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.

[stat_sas · Accepted solution]

proc sql;

select * from marks

group by subject

having count=max(count);

quit;

[annapurna]

thankyou stat@sas

its working