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.
Firm | year | month | sales |
1 | 2002 | 1 | |
1 | 2002 | 2 | |
1 | 2002 | 3 | |
1 | 2002 | 4 | |
1 | 2002 | 5 | |
1 | 2002 | 6 | |
1 | 2002 | 7 | |
1 | 2002 | 8 | |
1 | 2002 | 9 | |
1 | 2002 | 10 | |
1 | 2002 | 11 | |
1 | 2002 | 12 | |
1 | 2003 | 1 | |
1 | 2003 | 2 | |
1 | 2003 | 3 | |
… | … | ||
100 | 2011 | 12 |
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
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.