BookmarkSubscribeRSS Feed
TorTheHammer
Fluorite | Level 6

Hello, dear experts. 

I have a problem related to ranking growth rate in sas. 
I have computed growth rate as annual growth rate. The thing that I want to do next is to rank the previous 5 year growth rate, given the year of formation, which starts in 1968. Can someone please help me? I would be very thankful. 🙂


Data have: 

YEAR   ID   Growth Rate

1963     1        1%
1964     1        2%
1965     1        2%
1966     1        3%
1967     1        0,5%
1968     1        5%
1969     1        6%
1970     1        7% 

Data want:

YEAR   ID   Growth Rate  RANK1  RANK2 RANK3 RANK4 RANK5

1963     1        1%               
1964     1        2%
1965     1        2%
1966     1        3%
1967     1        0,5%
1968     1        5%                3%         2%         2%       1%        0,5%
1969     1        6%                5%         3%         2%       2%        0,5%
1970     1        7%                6%         5%         3%       2%        0,5%

This is just an example, by orginal want file has many years, IDs, and growth rates. 

I also wonder about what to do with "tied" ranks, like the 2% in both. 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep!!

Well, this should give you an idea (note the rank is in reverse):

data have;
  input YEAR ID Growth_Rate;
datalines;
1963 1 1
1964 1 2
1965 1 2
1966 1 3
1967 1 0.5
1968 1 5
1969 1 6
1970 1 7
;
run;

data want;
  set have;
  array rank{5} 8.;
  rank{1}=lag1(growth_rate);
  rank{2}=lag2(growth_rate);
  rank{3}=lag3(growth_rate);
  rank{4}=lag4(growth_rate);
  rank{5}=lag5(growth_rate);
  call sortn(of rank:);
run;
TorTheHammer
Fluorite | Level 6
Hello, thank you for a solid answer!
The code does what I want, but I have a struggle related to "by" groups. For example if I have two ID's, then one's growth_rate jumps over to another ones ID. I tried to use "by" statement, but this doesn't solve the problem. 😞
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You may need an if as well:

data have;
  input YEAR ID Growth_Rate;
datalines;
1963 1 1
1964 1 2
1965 1 2
1966 1 3
1967 1 0.5
1968 1 5
1969 1 6
1970 1 7
;
run;

data want;
  set have;
by id; array rank{5} 8.;
if first.id then call missing(of rank:); if id=lag1(id) then rank{1}=lag1(growth_rate); if id=lag2(id) then rank{2}=lag2(growth_rate); if id=lag3(id) then rank{3}=lag3(growth_rate); if id=lag4(id) then rank{4}=lag4(growth_rate); if id=lag5(id) then rank{5}=lag5(growth_rate); call sortn(of rank:); run;
Ksharp
Super User
data have;
  input YEAR ID Growth_Rate;
datalines;
1963 1 1
1964 1 2
1965 1 2
1966 1 3
1967 1 0.5
1968 1 5
1969 1 6
1970 1 7
;
run;

data want;
  set have;
  array rank{5} rank5-rank1;
  array r{0:4} _temporary_;
  if _n_ gt 5 then do;
   do i=1 to 5;
    rank{i}=r{i-1};
   end;
   call sortn(of rank{*});
  end;
  r{mod(_n_,5)}=Growth_Rate;
 drop i;
 run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1013 views
  • 3 likes
  • 3 in conversation