Rank variables by group

[J_J_J]

Hi!

I'm trying to find the rank for the different count on values by month and year and to have first 3 positions (descending by num) for the each month. I have only one year data. Help me please to this in right way.

data have;
month=1; code='BB1111111'; year=2023; name='BB'; num=4; output;
month=1; code='AA1111111'; year=2023; name='AA'; num=8; output;
month=1; code='FF1111111'; year=2023; name='FF'; num=1; output;
month=1; code='CC1111111'; year=2023; name='CC'; num=5134; output;
month=1; code='DD1111111'; tyear=2023; name='DD'; num=525; output;
month=1; code='EE1111111'; year=2023; name='EE'; num=125; output;
month=2; code='QQ1111111'; year=2023; name='QQ'; num=4; output;
month=2; code='CC1111111'; tyear=2023; name='CC'; num=5321; output;
month=2; code='DD1111111'; year=2023; name='DD'; num=256; output;
month=2; code='WW1111111'; year=2023;name='WW'; num=151; output;

month=3; code='BB1111111'; year=2023; name='AA'; num=12; output;
month=3; code='CC1111111'; year=2023; name='CC'; num=2860; output;
month=3; code='CC1111111'; year=2023; name='DD'; num=655; output;
month=3; code='CC1111111'; year=2023; name='EE'; num=62; output;
month=4; code='AA1111111'; year=2023; name='AA'; num=2; output;
month=4; code='BB1111111'; year=2023; name='BB'; num=12; output;
month=4; code='CC1111111'; year=2023;name='CC'; num=1962; output;
month=4; code='CC1111111'; tyear=2023;name='DD'; num=865; output;
month=4; code='CC1111111'; year=2023;name='EE'; num=112; output;
run;

output I need:

month	year	code	name	num	rank
1	2023	CC1111111	CC	5134	1
1	2023	DD1111111	DD	525	2
1	2023	EE1111111	EE	125	3
2	2023	CC1111111	CC	5321	1
2	2023	DD1111111	DD	256	2
2	2023	WW1111111	WW	151	3
etc.	...	...	...	...	...

[PaigeMiller · Accepted solution]

You can sort the data by descending count, then take the first 3 records.

Or you can use PROC RANK and just keep the records where the rank is less than or equal to 3. 

proc rank data=have descending out=want ties=low;
     by month;
     var num;
     ranks num_ranked;
run;

Note: these two methods will not produce the same results in the case of ties.

[J_J_J]

Thank you, Paige Miller!