Ranking a variable while accounting for subgroup

[r4321]

Hello all, 

I have a dataset of firms across multiple years. I'm trying to rank them by their size while accounting for year. Firms would change their rankings each year according to how much their size changed. 

For example, made up data for illustration: 

year   firm            size       rank

1997  Walmart     2000       2

1997  Microsoft   3000       1

1997  GM            1000       3

1998  Walmart     3000       1

1998  Microsoft    2000       2

1998  GM            1000       3

1999  Walmart     2000       2

1999  Microsoft    1000      3

1999  GM            3000        1

Im sure the code is quite simple, but I've gotten hung up on it and couldnt find a solution. If anyone could help, I'd appreciate it. 

Thanks. 

[Jagadishkatam · Accepted solution]

Consider that there is no rank variable in your dataset. Then sort by year and descending size, this will sort the firms by year and size and brings the firm with more size to the top. Then we could derive the rank as below

proc sort data=have;
by year descending size;
run;

data want;
set have;
by year descending  size;
retain rank;
if first.year then rank=1;
else rank=rank+1;
run;

[r4321]

Thank you!

[Ksharp]

Or try PROC RANK.


data have;
input year   firm     : $20.       size ;
cards;
1997  Walmart     2000       2
1997  Microsoft   3000       1
1997  GM            1000       3
1998  Walmart     3000       1
1998  Microsoft    2000       2
1998  GM            1000       3
1999  Walmart     2000       2
1999  Microsoft    1000      3
1999  GM            3000        1
;
run;

proc rank data=have out=want  descending;
by year;
var size;
ranks rank;
run;