topic Re: Growth rate and ranking in SAS Programming

Growth rate and ranking

TorTheHammer — Fri, 09 Jun 2017 13:39:33 GMT

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.

Re: Growth rate and ranking

RW9 — Fri, 09 Jun 2017 13:50:49 GMT

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;

Re: Growth rate and ranking

Ksharp — Fri, 09 Jun 2017 14:35:32 GMT

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;

Re: Growth rate and ranking

TorTheHammer — Fri, 09 Jun 2017 15:06:31 GMT

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. 😞

Re: Growth rate and ranking

RW9 — Fri, 09 Jun 2017 16:11:52 GMT

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;