The SAS Output Delivery System and reporting techniques

Dividing Vertically (Rate of change)

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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?

YearPercentage
14.71%
26.17%
37.82%
49.90%
511.69%

YearPercentageChange from Prev Year
14.71%N/A
26.17%31.00%
37.82%26.74%
49.90%26.60%
511.69%18.08%

Accepted Solutions
Solution
‎02-27-2014 10:47 AM
Grand Advisor
Posts: 16,303

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;

View solution in original post


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
Grand Advisor
Posts: 16,303

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: 45

Re: Dividing Vertically (Rate of change)

Thank you so much!

This lag function is everything that I needed.

(I wish I knew sql)

Post a Question
Discussion Stats
  • 4 replies
  • 443 views
  • 4 likes
  • 3 in conversation