topic Re: Dividing Vertically (Rate of change) in ODS and Base Reporting

Dividing Vertically (Rate of change)

Greek — Thu, 27 Feb 2014 13:50:25 GMT

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%

Re: Dividing Vertically (Rate of change)

DBailey — Thu, 27 Feb 2014 14:10:34 GMT

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;

Re: Dividing Vertically (Rate of change)

Reeza — Thu, 27 Feb 2014 15:47:18 GMT

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;

Re: Dividing Vertically (Rate of change)

DBailey — Thu, 27 Feb 2014 15:56:14 GMT

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

Re: Dividing Vertically (Rate of change)

Greek — Thu, 27 Feb 2014 17:55:26 GMT

Thank you so much!

This lag function is everything that I needed.

(I wish I knew sql)