Calculate growth rate by comparing current vs previous year

[harshpatel]

Hello,

I want to calculate growth rate by comparing current vs previous year in SAS coding

((Current year - Previous year)/Previous year) * 100

I have table structure like

Year     Item        Sale    

2018      Rice        2500     

2018      Ghee      2000     

2019      Rice        2000      

2019      Ghee      1900     

2020       Rice       2000     

2020        Ghee     1500    

Now i want to make a new column named as previous year column adjacent to sale

e.g 2018 year the value should be 0

but against 2019 year the value should be of 2018 likewise

So i wanted below structure

Year     Item        Sale     PreviousYear

2018      Rice        2500     0

2018      Ghee      2000     0

2019      Rice        2000      2500

2019      Ghee      1900      2000

2020       Rice       2000     2000  

2020        Ghee     1500    1900

Kindly help me on this

Regards,

Harsh

[PeterClemmensen]

Do you always have exactly two items within each year?

[harshpatel]

Hi Peter,
Thanks for your response
It may be different item not exact same for each year

[ballardw]

---

@harshpatel wrote:
Hi Peter,
Thanks for your response
It may be different item not exact same for each year

---

If the items change from year to year then something like this:

Proc sort data=have;
   by item year;
run;

data want;
   set have;
   by item ;
   change = 100* (sale -lag(sale))/lag(sale);
   if first.item then change=0;
run;

/*if you want the data in the "original" order*/
proc sort data=want;
   by year item;
run;

Get  like items together in year order so the change is calculated correctly. Use BY group processing so you know when the item changes and can set the change to 0.

Re-sort if the original order is important.

[harshpatel]

Thanks,

It works for me

Regards,

Harsh 

[PeterClemmensen]

If so then simply do

data have;
input Year Item $ Sale;
datalines;
2018 Rice 2500 
2018 Ghee 2000 
2019 Rice 2000 
2019 Ghee 1900 
2020 Rice 2000 
2020 Ghee 1500 
;

data want;
   set have;
   PreviousYear = lag2(Sale);
run;

[Sajid01]

I have the following. You can refine the code. The result can easily be validated.
The variables preceded by g are growth rates between the two years and representing the percentage of change.

---

data have;
input Year Item $ Sale;
datalines;
2018 Rice 2500
2018 Ghee 2000
2019 Rice 2000
2019 Ghee 1900
2020 Rice 2000
2020 Ghee 1500
;
proc sort data=have;
by Item;
run;
proc transpose data=have out=want (drop=_NAME_);
by Item;
ID Year;
var Sale;
run;
data want;
set want ;
g1819=100*(_2019-_2018)/_2018;
g1920=100*(_2020-_2019)/2019;
run;
proc print;
run;
The result will be like this

Obs Item   _2018    _2019   _2020   g1819     g1920
1     Ghee    2000     1900    1500         -5     -19.8118
2     Rice     2500     2000    2000       -20        0.0000

[PGStats · Accepted solution]

May I propose:

proc sql;
create table want as
select
    a.*,
    coalesce(b.sale, 0) as previousSale,
    coalesce((a.sale-b.sale)/b.sale, 0) as growth format=percentn8.1
from 
    have as a left join
    have as b on a.item=b.item and a.year = b.year+1
order by year, item;
quit;

[harshpatel]

Thanks, it works for me

Regards,

Harsh