topic Re: Calculate 3 Year Growth in SAS Programming

Calculate 3 Year Growth

sasmaverick — Wed, 06 Jan 2016 09:24:26 GMT

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
Google 120 2010
Google 190 2011
Google 320 2012
Google 440 2013
;
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,

Re: Calculate 3 Year Growth

RW9 — Wed, 06 Jan 2016 09:42:40 GMT

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/highlight/true#M1053

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

Re: Calculate 3 Year Growth

Ksharp — Wed, 06 Jan 2016 09:54:04 GMT

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
Google 120 2010
Google 190 2011
Google 320 2012
Google 440 2013
;
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;

Re: Calculate 3 Year Growth

ad123123 — Wed, 06 Jan 2016 11:24:54 GMT

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
Google 120 2010
Google 190 2011
Google 320 2012
Google 440 2013
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.

Re: Calculate 3 Year Growth

Reeza — Wed, 06 Jan 2016 15:40:08 GMT

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;

Re: Calculate 3 Year Growth

PGStats — Thu, 07 Jan 2016 04:11:26 GMT

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
Google 120 2010
Google 190 2011
Google 320 2012
Google 440 2013
;

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;