DATA Step, Macro, Functions and more

Growth rate and ranking

Reply
Occasional Contributor
Posts: 6

Growth rate and ranking

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. Smiley Happy


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. 

Super User
Super User
Posts: 7,997

Re: Growth rate and ranking

Posted in reply to TorTheHammer

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;
Occasional Contributor
Posts: 6

Re: Growth rate and ranking

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. Smiley Sad
Super User
Super User
Posts: 7,997

Re: Growth rate and ranking

Posted in reply to TorTheHammer

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;
Super User
Posts: 10,047

Re: Growth rate and ranking

Posted in reply to TorTheHammer
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;
Ask a Question
Discussion stats
  • 4 replies
  • 194 views
  • 3 likes
  • 3 in conversation