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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.