DATA Step, Macro, Functions and more

calculating y-o-y growth

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

calculating y-o-y growth

Dear SAS Users,

I have panel sales data as follows for different firms. How can I calculate year-on-year sales growth? I want something like growth=100*dif12(sales)/lag12(sales) but I don't want dif12 to compare values of different firms.

Thanks for any help in advance.

Firmyearmonthsales
120021
120022
120023
120024
120025
120026
120027
120028
120029
1200210
1200211
1200212
120031
120032
120033
100201112

Accepted Solutions
Solution
‎11-13-2012 01:26 AM
Super User
Posts: 10,020

Re: calculating y-o-y growth

ArthurT,

Your code would not be able to work, If there are some missing monthes or duplicated monthes. But yours is a fast solution.

data have;
input Firm     year     month     sales ;
date=mdy(month,1,year);
format date yymmdd.;
cards;
1     2002     1     1
1     2002     2     1
1     2002     3     1
1     2002     4     1
1     2002     5     1
1     2002     6     1
1     2002     7     1
1     2002     8     1
1     2002     9     1
1     2002     10     1
1     2002     11     1
1     2002     12     1
1     2003     1     1
1     2003     2     1
1     2003     3    1
;
run;
proc sql;
 create table want as
  select *,((select sales from have where firm=h.firm and date=intnx('month',h.date,-12)) -
            (select sales from have where firm=h.firm and date=intnx('month',h.date,-13)) ) /
            (select sales from have where firm=h.firm and date=intnx('month',h.date,-12)) as growth
   from have as h;
quit;


Ksharp

View solution in original post


All Replies
PROC Star
Posts: 7,468

Re: calculating y-o-y growth

Does the following do what you want?:

data want;

  set have;

  by firm;

  if first.firm then counter=1;

  else counter+1;

  growth=ifn(counter gt 12,100*dif12(sales)/lag12(sales),.);

run;

Solution
‎11-13-2012 01:26 AM
Super User
Posts: 10,020

Re: calculating y-o-y growth

ArthurT,

Your code would not be able to work, If there are some missing monthes or duplicated monthes. But yours is a fast solution.

data have;
input Firm     year     month     sales ;
date=mdy(month,1,year);
format date yymmdd.;
cards;
1     2002     1     1
1     2002     2     1
1     2002     3     1
1     2002     4     1
1     2002     5     1
1     2002     6     1
1     2002     7     1
1     2002     8     1
1     2002     9     1
1     2002     10     1
1     2002     11     1
1     2002     12     1
1     2003     1     1
1     2003     2     1
1     2003     3    1
;
run;
proc sql;
 create table want as
  select *,((select sales from have where firm=h.firm and date=intnx('month',h.date,-12)) -
            (select sales from have where firm=h.firm and date=intnx('month',h.date,-13)) ) /
            (select sales from have where firm=h.firm and date=intnx('month',h.date,-12)) as growth
   from have as h;
quit;


Ksharp

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1536 views
  • 3 likes
  • 3 in conversation