topic Calculate growth rate by comparing current vs previous year in SAS Programming

Calculate growth rate by comparing current vs previous year

harshpatel — Tue, 16 Mar 2021 06:10:19 GMT

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

Re: Calculate growth rate by comparing current vs previous year

PeterClemmensen — Tue, 16 Mar 2021 06:12:17 GMT

Do you always have exactly two items within each year?

Re: Calculate growth rate by comparing current vs previous year

PeterClemmensen — Tue, 16 Mar 2021 06:13:41 GMT

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;

Re: Calculate growth rate by comparing current vs previous year

harshpatel — Tue, 16 Mar 2021 13:45:37 GMT

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

Re: Calculate growth rate by comparing current vs previous year

ballardw — Tue, 16 Mar 2021 14:36:50 GMT

@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.

Re: Calculate growth rate by comparing current vs previous year

Sajid01 — Tue, 16 Mar 2021 16:03:49 GMT

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

Re: Calculate growth rate by comparing current vs previous year

PGStats — Tue, 16 Mar 2021 17:31:24 GMT

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;

Re: Calculate growth rate by comparing current vs previous year

harshpatel — Wed, 17 Mar 2021 04:02:42 GMT

Thanks, it works for me

Regards,

Harsh

Re: Calculate growth rate by comparing current vs previous year

harshpatel — Wed, 17 Mar 2021 04:24:43 GMT

Thanks,

It works for me

Regards,

Harsh