dear all
i have to compute "change in sales" as the difference between current year and previous year values of variable on rolling basis in a panel data in SAS
the format of my dataset is as follows
Company_Name | year | sales | change_sales |
20 Microns Ltd. | 2005 | 565.2 | |
20 Microns Ltd. | 2006 | 668.9 | 103.7 |
20 Microns Ltd. | 2007 | 869.1 | 200.2 |
20 Microns Ltd. | 2008 | 1067.8 | 198.7 |
20 Microns Ltd. | 2009 | 1374.9 | 307.1 |
20 Microns Ltd. | 2010 | 1768.2 | 393.3 |
20 Microns Ltd. | 2011 | 2349.8 | 581.6 |
20 Microns Ltd. | 2012 | 2626.2 | 276.4 |
3I Infotech Ltd. | 2005 | 2064.7 | |
3I Infotech Ltd. | 2006 | 2755.5 | 690.8 |
3I Infotech Ltd. | 2007 | 3313.1 | 557.6 |
3I Infotech Ltd. | 2008 | 4451 | 1137.9 |
3I Infotech Ltd. | 2009 | 5249.6 | 798.6 |
3I Infotech Ltd. | 2010 | 5195.3 | -54.3 |
3I Infotech Ltd. | 2011 | 5479.2 | 283.9 |
3I Infotech Ltd. | 2012 | 5117.8 | -361.4 |
3M India Ltd. | 2005 | 2552 | |
3M India Ltd. | 2006 | 3780.5 | 1228.5 |
3M India Ltd. | 2007 | 5030.7 | 1250.2 |
3M India Ltd. | 2008 | 6066.1 | 1035.4 |
3M India Ltd. | 2009 | 7413 | 1346.9 |
3M India Ltd. | 2010 | 10912.8 | 3499.8 |
3M India Ltd. | 2011 | 11891.2 | 978.4 |
3M India Ltd. | 2012 | 14033 | 2141.8 |
thanks in advance
/* UNTESTED CODE */
data want;
set have;
by company_name;
prev sales=lag(sales);
if not first.company_name then change_sales=sales-prev_sales;
drop prev_sales;
run;
/* UNTESTED CODE */
data want;
set have;
by company_name;
prev sales=lag(sales);
if not first.company_name then change_sales=sales-prev_sales;
drop prev_sales;
run;
data want;
set have;
by company_name year;
change_sales=dif(sales );
if first.company_name then call missing(change_sales);
run;
data have;
input Company_Name $16. year sales;* change_sales;
cards;
20 Microns Ltd. 2005 565.2 .
20 Microns Ltd. 2006 668.9 103.7
20 Microns Ltd. 2007 869.1 200.2
20 Microns Ltd. 2008 1067.8 198.7
20 Microns Ltd. 2009 1374.9 307.1
20 Microns Ltd. 2010 1768.2 393.3
20 Microns Ltd. 2011 2349.8 581.6
20 Microns Ltd. 2012 2626.2 276.4
3I Infotech Ltd. 2005 2064.7 .
3I Infotech Ltd. 2006 2755.5 690.8
3I Infotech Ltd. 2007 3313.1 557.6
3I Infotech Ltd. 2008 4451 1137.9
3I Infotech Ltd. 2009 5249.6 798.6
3I Infotech Ltd. 2010 5195.3 -54.3
3I Infotech Ltd. 2011 5479.2 283.9
3I Infotech Ltd. 2012 5117.8 -361.4
3M India Ltd. 2005 2552 .
3M India Ltd. 2006 3780.5 1228.5
3M India Ltd. 2007 5030.7 1250.2
3M India Ltd. 2008 6066.1 1035.4
3M India Ltd. 2009 7413 1346.9
3M India Ltd. 2010 10912.8 3499.8
3M India Ltd. 2011 11891.2 978.4
3M India Ltd. 2012 14033 2141.8
;
data want;
set have;
by company_name year;
change_sales=dif(sales );
if first.company_name then call missing(change_sales);
run;
data have;
input Company_Name $16. year sales;* change_sales;
cards;
20 Microns Ltd. 2005 565.2 .
20 Microns Ltd. 2006 668.9 103.7
20 Microns Ltd. 2007 869.1 200.2
20 Microns Ltd. 2008 1067.8 198.7
20 Microns Ltd. 2009 1374.9 307.1
20 Microns Ltd. 2010 1768.2 393.3
20 Microns Ltd. 2011 2349.8 581.6
20 Microns Ltd. 2012 2626.2 276.4
3I Infotech Ltd. 2005 2064.7 .
3I Infotech Ltd. 2006 2755.5 690.8
3I Infotech Ltd. 2007 3313.1 557.6
3I Infotech Ltd. 2008 4451 1137.9
3I Infotech Ltd. 2009 5249.6 798.6
3I Infotech Ltd. 2010 5195.3 -54.3
3I Infotech Ltd. 2011 5479.2 283.9
3I Infotech Ltd. 2012 5117.8 -361.4
3M India Ltd. 2005 2552 .
3M India Ltd. 2006 3780.5 1228.5
3M India Ltd. 2007 5030.7 1250.2
3M India Ltd. 2008 6066.1 1035.4
3M India Ltd. 2009 7413 1346.9
3M India Ltd. 2010 10912.8 3499.8
3M India Ltd. 2011 11891.2 978.4
3M India Ltd. 2012 14033 2141.8
;
data want;
do _n_=1 by 1 until(last.company_name);
set have;
by company_name year;
if _n_>1 then change_sales=sales-_iorc_;
output;
_iorc_=sales;
end;
run;
Fun part of continuous data(i,e year variable sweetly sorted by id year) is a piece of cake to play with even when using SQL
data have;
input Company_Name $16. year sales;* change_sales;
cards;
20 Microns Ltd. 2005 565.2 .
20 Microns Ltd. 2006 668.9 103.7
20 Microns Ltd. 2007 869.1 200.2
20 Microns Ltd. 2008 1067.8 198.7
20 Microns Ltd. 2009 1374.9 307.1
20 Microns Ltd. 2010 1768.2 393.3
20 Microns Ltd. 2011 2349.8 581.6
20 Microns Ltd. 2012 2626.2 276.4
3I Infotech Ltd. 2005 2064.7 .
3I Infotech Ltd. 2006 2755.5 690.8
3I Infotech Ltd. 2007 3313.1 557.6
3I Infotech Ltd. 2008 4451 1137.9
3I Infotech Ltd. 2009 5249.6 798.6
3I Infotech Ltd. 2010 5195.3 -54.3
3I Infotech Ltd. 2011 5479.2 283.9
3I Infotech Ltd. 2012 5117.8 -361.4
3M India Ltd. 2005 2552 .
3M India Ltd. 2006 3780.5 1228.5
3M India Ltd. 2007 5030.7 1250.2
3M India Ltd. 2008 6066.1 1035.4
3M India Ltd. 2009 7413 1346.9
3M India Ltd. 2010 10912.8 3499.8
3M India Ltd. 2011 11891.2 978.4
3M India Ltd. 2012 14033 2141.8
;
proc sql;
create table want as
select a.*,a.sales-b.sales as change_sales
from have a left join have b
on a.company_name =b.company_name and b.year<a.year
group by a.company_name, a.year,a.sales
having max(b.year)=b.year
order by a.company_name,a.year;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.