topic Re: Ranking a variable while accounting for subgroup in SAS Data Management

Ranking a variable while accounting for subgroup

r4321 — Sun, 18 Dec 2016 06:36:35 GMT

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.

Re: Ranking a variable while accounting for subgroup

Jagadishkatam — Sun, 18 Dec 2016 06:54:06 GMT

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;

Re: Ranking a variable while accounting for subgroup

r4321 — Sun, 18 Dec 2016 21:21:54 GMT

Thank you!

Re: Ranking a variable while accounting for subgroup

Ksharp — Mon, 19 Dec 2016 02:56:06 GMT


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;