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

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
PGStats
Opal | Level 21

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;

PGStats_0-1615915875149.png

 

PG

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

Do you always have exactly two items within each year?

harshpatel
Quartz | Level 8
Hi Peter,
Thanks for your response
It may be different item not exact same for each year
ballardw
Super User

@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
Quartz | Level 8

Thanks,

It works for me

 

Regards,

Harsh 

PeterClemmensen
Tourmaline | Level 20

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

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
Opal | Level 21

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;

PGStats_0-1615915875149.png

 

PG
harshpatel
Quartz | Level 8

Thanks, it works for me

 

Regards,

Harsh

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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