Contributor
Posts: 69

# Calculate 3 Year Growth

I have the following dataset:

data have;
input Company : \$8. Revenue Year;
cards;
HP 100 2012
HP 200 2013
HP 300 2014
Sun 200 2011
Sun 400 2012
Sun 500 2013
Sun 600 2014
Sun 800 2015
Apple 150 2014
Apple 250 2015
;
run;

I need to calculate 3 year growth rate for each company. Eg. for HP, no three year growth. For Sun, 2015-2012/2012 for Google, 2013-2010/2010 and so on. How do I do this?

Thanks,

Super User
Posts: 9,599

## Re: Calculate 3 Year Growth

Posted in reply to sasmaverick

Well, haven't tested this, but just merge the year + 3 data back onto the original data and calculate, something like:

```proc sql;
create table WANT as
select  A.*,
case when B.VAL ne . then A.VAL - (B.VAL / B.VAL)
else . end as GROWTH
from    HAVE A
left join HAVE B
on       A.COMPANY=B.COMPANY
and     A.YEAR=(B.YEAR+3);
quit;```

Also note, you can search the forums, some of the initial posts may be useful:

https://communities.sas.com/t5/SAS-Procedures/calculate-growth-rate-within-groupings/m-p/10660/highl...

https://communities.sas.com/t5/Base-SAS-Programming/Growth-By-Product-Per-Quarter/m-p/50313/highligh...

Super User
Posts: 10,784

## Re: Calculate 3 Year Growth

Posted in reply to sasmaverick
``````data have;
input Company : \$8. Revenue Year;
cards;
HP 100 2012
HP 200 2013
HP 300 2014
Sun 200 2011
Sun 400 2012
Sun 500 2013
Sun 600 2014
Sun 800 2015
Apple 150 2014
Apple 250 2015
;
run;
proc sql;
select *,((select Revenue from have where Company=a.Company and Year=a.Year+3)-a.Revenue)/a.Revenue  as r
from have as a;
quit;``````
Occasional Contributor
Posts: 18

## Re: Calculate 3 Year Growth

Hi,

You can also try following solution:-

data have;
input Company : \$8. Revenue Year;
cards;
HP 100 2012
HP 200 2013
HP 300 2014
Sun 200 2011
Sun 400 2012
Sun 500 2013
Apple 150 2014
Apple 250 2015
Sun 600 2014
Sun 800 2015
;
run;

proc sort data=have;
by Company Year;
run;

proc sql noprint;
Select
"'" || strip(Company) || "'" into : company_Name separated by ','
From
have
group by Company
having count(Company) > 3
;
quit;

%put company_Name =&company_Name;

data want;
set have (where= (Company in(&company_Name)));
by company notsorted;
retain Total_revenue 0;

if first.company then
Total_revenue=0;
Total_revenue = revenue + Total_revenue;
run;

Let me know if it works for you.

Super User
Posts: 23,735

## Re: Calculate 3 Year Growth

Posted in reply to sasmaverick

If you know you have data for all years and no gaps in time this data step also works using the lag function directly.

``````proc sort data=have;
by company year;
run;

data want;
set have;
by company year;
if first.company then count=1;
else count+1;
return=(lag3(revenue)-revenue)/lag3(revenue);
if count<=3 then return=.;
format return percent8.2;
run;``````
Posts: 5,531

## Re: Calculate 3 Year Growth

Posted in reply to sasmaverick

It can be done efficiently with a data step, using an array:

``````data have;
input Company : \$8. Revenue Year;
cards;
HP 100 2012
HP 200 2013
HP 300 2014
Sun 200 2011
Sun 400 2012
Sun 500 2013
Sun 800 2015    <- Skipped 2014
Apple 150 2014
Apple 250 2015
;

proc sort data=have; by company year; run;

data growth;
set have; by company;
array _r{1950:2050}  _temporary_;
if first.company then call missing(of _r{*});
_r{year} = revenue;
if last.company then
if not missing(_r{year-3}) then do;
growth3y = (revenue-_r{year-3}) / _r{year-3};
output;
end;
format growth3y percentn8.2;
run;

proc print data=growth noobs; run;
``````
PG
Discussion stats
• 5 replies
• 307 views
• 2 likes
• 6 in conversation