BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Calculate growth rate by comparing current vs previous year

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

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: Calculate growth rate by comparing current vs previous year

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;``````

PG
8 REPLIES 8
Tourmaline | Level 20

## Re: Calculate growth rate by comparing current vs previous year

Do you always have exactly two items within each year?

Quartz | Level 8

## Re: Calculate growth rate by comparing current vs previous year

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

## Re: Calculate growth rate by comparing current vs previous year

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

Quartz | Level 8

## Re: Calculate growth rate by comparing current vs previous year

Thanks,

It works for me

Regards,

Harsh

Tourmaline | Level 20

## Re: Calculate growth rate by comparing current vs previous year

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;``````
Meteorite | Level 14

## Re: Calculate growth rate by comparing current vs previous year

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

Opal | Level 21

## Re: Calculate growth rate by comparing current vs previous year

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;``````

PG
Quartz | Level 8

## Re: Calculate growth rate by comparing current vs previous year

Thanks, it works for me

Regards,

Harsh

Discussion stats
• 8 replies
• 2407 views
• 2 likes
• 5 in conversation