Dividing Vertically (Rate of change)

[Greek]

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%

[DBailey]

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;

[Reeza · Accepted solution]

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;

[DBailey]

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

[Greek]

Thank you so much!

This lag function is everything that I needed.

(I wish I knew sql)