SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Ranking a variable while accounting for subgroup

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Ranking a variable while accounting for subgroup

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. 


Accepted Solutions
Solution
‎12-18-2016 04:21 PM
Trusted Advisor
Posts: 1,137

Re: Ranking a variable while accounting for subgroup

[ Edited ]

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;

 

Thanks,
Jag

View solution in original post


All Replies
Solution
‎12-18-2016 04:21 PM
Trusted Advisor
Posts: 1,137

Re: Ranking a variable while accounting for subgroup

[ Edited ]

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;

 

Thanks,
Jag
Contributor
Posts: 51

Re: Ranking a variable while accounting for subgroup

Posted in reply to Jagadishkatam
Thank you!
Super User
Posts: 10,035

Re: Ranking a variable while accounting for subgroup


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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 249 views
  • 1 like
  • 3 in conversation