Solved
Contributor
Posts: 48

# Dividing Vertically (Rate of change)

Good morning,

I have the following table and I would like to calculate the rate of change between individual years. The second table is my desired output. It can be done easily in excel, but it is a small step of a large sas code and I would rather not interrupt the process... Any ideas?

 Year Percentage 1 4.71% 2 6.17% 3 7.82% 4 9.90% 5 11.69%

 Year Percentage Change from Prev Year 1 4.71% N/A 2 6.17% 31.00% 3 7.82% 26.74% 4 9.90% 26.60% 5 11.69% 18.08%

Accepted Solutions
Solution
‎02-27-2014 10:47 AM
Super User
Posts: 23,323

## Re: Dividing Vertically (Rate of change)

Use a data step, with a lag function to reference the previous cell.

data want;

set have;

pct_change=percentage/lag1(percentage)-1;

format pct_change percent8.2;

run;

All Replies
Super Contributor
Posts: 578

## Re: Dividing Vertically (Rate of change)

proc sql;

create table want as

select

t1.Year,

t1.Percentage,

t1.percentage / t2.percentage as ChangeFromPrevYear

from

have t1

left outer join have t2

on t1.year-1=t2.year

quit;

Solution
‎02-27-2014 10:47 AM
Super User
Posts: 23,323

## Re: Dividing Vertically (Rate of change)

Use a data step, with a lag function to reference the previous cell.

data want;

set have;

pct_change=percentage/lag1(percentage)-1;

format pct_change percent8.2;

run;

Super Contributor
Posts: 578

## Re: Dividing Vertically (Rate of change)

Your approach might be easier for this case.  Most of the time I revert to sql as that's what I grew up with and it seems to be a bit more straightforward if you end up needing more complex criteria than just the previous record.  Also, the data must be sorted for the data step approach...not necessary for sql (although you probably would want it sorted just to help performance).

Contributor
Posts: 48

## Re: Dividing Vertically (Rate of change)

Thank you so much!

This lag function is everything that I needed.

(I wish I knew sql)

🔒 This topic is solved and locked.