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,
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:
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;
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.
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: