BookmarkSubscribeRSS Feed
sasmaverick
Obsidian | Level 7

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,

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ksharp
Super User
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;
ad123123
Fluorite | Level 6

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.

Reeza
Super User

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;
PGStats
Opal | Level 21

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;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1491 views
  • 2 likes
  • 6 in conversation