topic Re: how to compute the difference between current year and previous year values of variable? in New SAS User https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583661#M13999 <PRE><CODE class=" language-sas">data want; set have; by company_name year; change_sales=dif(sales ); if first.company_name then call missing(change_sales); run;</CODE></PRE> Sat, 24 Aug 2019 13:50:22 GMT novinosrin 2019-08-24T13:50:22Z how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583657#M13996 <P>dear all&nbsp;</P><P>i have to compute&nbsp;&nbsp;<STRONG>"change in sales"</STRONG> as the difference between current year and previous year values of variable on rolling basis in a panel data in SAS</P><P>the format of my dataset is as follows</P><TABLE><TBODY><TR><TD>Company_Name</TD><TD>year</TD><TD>sales&nbsp;</TD><TD>change_sales</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2005</TD><TD>565.2</TD><TD>&nbsp;</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2006</TD><TD>668.9</TD><TD>103.7</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2007</TD><TD>869.1</TD><TD>200.2</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2008</TD><TD>1067.8</TD><TD>198.7</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2009</TD><TD>1374.9</TD><TD>307.1</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1768.2</TD><TD>393.3</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2011</TD><TD>2349.8</TD><TD>581.6</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2012</TD><TD>2626.2</TD><TD>276.4</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2005</TD><TD>2064.7</TD><TD>&nbsp;</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2006</TD><TD>2755.5</TD><TD>690.8</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2007</TD><TD>3313.1</TD><TD>557.6</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2008</TD><TD>4451</TD><TD>1137.9</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2009</TD><TD>5249.6</TD><TD>798.6</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>5195.3</TD><TD>-54.3</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2011</TD><TD>5479.2</TD><TD>283.9</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2012</TD><TD>5117.8</TD><TD>-361.4</TD></TR><TR><TD>3M India Ltd.</TD><TD>2005</TD><TD>2552</TD><TD>&nbsp;</TD></TR><TR><TD>3M India Ltd.</TD><TD>2006</TD><TD>3780.5</TD><TD>1228.5</TD></TR><TR><TD>3M India Ltd.</TD><TD>2007</TD><TD>5030.7</TD><TD>1250.2</TD></TR><TR><TD>3M India Ltd.</TD><TD>2008</TD><TD>6066.1</TD><TD>1035.4</TD></TR><TR><TD>3M India Ltd.</TD><TD>2009</TD><TD>7413</TD><TD>1346.9</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>10912.8</TD><TD>3499.8</TD></TR><TR><TD>3M India Ltd.</TD><TD>2011</TD><TD>11891.2</TD><TD>978.4</TD></TR><TR><TD>3M India Ltd.</TD><TD>2012</TD><TD>14033</TD><TD>2141.8</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>thanks in advance&nbsp;</P> Sat, 24 Aug 2019 13:40:44 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583657#M13996 srikanthyadav44 2019-08-24T13:40:44Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583659#M13997 <PRE><CODE class=" language-sas">/* 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;</CODE></PRE> Sat, 24 Aug 2019 13:44:44 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583659#M13997 PaigeMiller 2019-08-24T13:44:44Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583661#M13999 <PRE><CODE class=" language-sas">data want; set have; by company_name year; change_sales=dif(sales ); if first.company_name then call missing(change_sales); run;</CODE></PRE> Sat, 24 Aug 2019 13:50:22 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583661#M13999 novinosrin 2019-08-24T13:50:22Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583662#M14000 <PRE><CODE class=" language-sas">data have; input Company_Name $16. year sales;*&nbsp; 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;</CODE></PRE> Sat, 24 Aug 2019 13:53:39 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583662#M14000 novinosrin 2019-08-24T13:53:39Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583663#M14001 <PRE><CODE class=" language-sas">data have; input Company_Name $16. year sales;*&nbsp; 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_&gt;1 then change_sales=sales-_iorc_; output; _iorc_=sales; end; run;</CODE></PRE> Sat, 24 Aug 2019 13:59:22 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583663#M14001 novinosrin 2019-08-24T13:59:22Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583667#M14003 <P>Fun part of continuous data(i,e year variable sweetly sorted <EM><STRONG>by id year)</STRONG></EM> is a piece of cake to play with even when using SQL</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data have; input Company_Name $16. year sales;*&nbsp; 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&lt;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;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> Sat, 24 Aug 2019 14:21:37 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583667#M14003 novinosrin 2019-08-24T14:21:37Z Re: how to compute the difference between current year and previous year values of variable? https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583699#M14010 thank you Mr. Paige Miller for your quick reply.<BR />your SAS code is working excellently with one small correction i.e.,<BR />prev_sales=lag(sales);<BR /><BR />thanks a lot<BR /><BR /> Sat, 24 Aug 2019 19:16:24 GMT https://communities.sas.com/t5/New-SAS-User/how-to-compute-the-difference-between-current-year-and-previous/m-p/583699#M14010 srikanthyadav44 2019-08-24T19:16:24Z