how to compute the difference between current year and previous year values of variable?

[srikanthyadav44]

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 

[PaigeMiller · Accepted solution]

/* 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;

[srikanthyadav44]

thank you Mr. Paige Miller for your quick reply.
your SAS code is working excellently with one small correction i.e.,
prev_sales=lag(sales);

thanks a lot

[novinosrin]

data want;
set have;
by company_name year;
change_sales=dif(sales );
if first.company_name then call missing(change_sales);
run;

[novinosrin]

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;

[novinosrin]

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;

[novinosrin]

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;