## Calculating Monthly Percent change

Solved
Occasional Contributor
Posts: 17

# Calculating Monthly Percent change

I want to calculate the monthly percent change. I tried using the lag function but I got all zeroes, I am guessing it's because there are no values in the obs above. Below is a sample desired output. Any assistance is greatly appreciated! :-)

Mon      Date             x      mnthly_x       mth_pct_change

1      1/2/13            10

1      1/9/13            5

1      1/31/13           10            25                                 0

2      2/6/13            8

2      2/17/13           6

2      2/28/13          13             27                                0.08

3      3/13/13          20

3       3/20/13          2

3       3/31/13         15           37                                   0.37

4       4/3/13           5

4       4/30/13      40                 45                               0.22

Accepted Solutions
Solution
‎05-29-2014 10:24 AM
Super User
Posts: 10,784

## Re: Calculating Monthly Percent change

```data have;
input Mon      Date :mmddyy8.  x;
format date mmddyy10.;
datalines;
1      1/2/13      10
1      1/9/13       5
1      1/31/13     10
2      2/6/13       8
2      2/17/13      6
2      2/28/13     13
3      3/13/13     20
3      3/20/13      2
3      3/31/13     15
4      4/3/13       5
4      4/30/13     40
;
run;
data have ;
set have;
by mon;
if first.mon then _x=0;
_x+x;
if last.mon then mnthy_x=_x;
drop _x;
run;
data temp;
set have(rename=(mnthy_x=_mnthy_x) where=(_mnthy_x is not missing));
mon=mon+1;
drop x date;
run;
data want;
merge have temp;
by mon;
mth_pct_change=(mnthy_x-_mnthy_x)/_mnthy_x;
drop _mnthy_x;
run;

```

Xia Keshan

All Replies
Super User
Posts: 23,771

## Re: Calculating Monthly Percent change

Safe to assume that your input is the first three variables?

And that the date is a SAS date (numeric with a date format)?

Occasional Contributor
Posts: 17

## Re: Calculating Monthly Percent change

yes to both questions.

Super User
Posts: 23,771

## Re: Calculating Monthly Percent change

The key is the retain statement/ability along with the BY statement.

Untested:

data want;

set have;

by month;

retain monthly_total prev_total;

if first.month then monthly_total=x;

else monthly_total=monthly_total+x;

if last.month then do;

mnthly_x=monthly_total;

mth_pct_chng=monthly_total/prev_total-1;

prev_total=monthly_total;

end;

run;

Posts: 5,540

## Re: Calculating Monthly Percent change

And once you understand how 's code works, you should explain why this also works :

data have;

input Mon      Date :mmddyy8.  x;

format date mmddyy10.;

datalines;

1      1/2/13      10

1      1/9/13       5

1      1/31/13     10

2      2/6/13       8

2      2/17/13      6

2      2/28/13     13

3      3/13/13     20

3      3/20/13      2

3      3/31/13     15

4      4/3/13       5

4      4/30/13     40

;

data want;

set have;

by Mon notsorted;  /* NOTSORTED in case there are many years */

if first.Mon then cumX = 0;

cumX + x;

if last.Mon then do;

mnthly_x = cumX;

mth_pct_change = mnthly_x/lag(mnthly_x) - 1;

end;

format mth_pct_change 6.2;

drop cumX;

run;

proc print data=want noobs; run;

Note the absence of a RETAIN statement. Hint: look up Sum Statement.

Note 2: I think this is the first time I use the LAG() function conditionally for a useful purpose.

PG

PG
Solution
‎05-29-2014 10:24 AM
Super User
Posts: 10,784

## Re: Calculating Monthly Percent change

```data have;
input Mon      Date :mmddyy8.  x;
format date mmddyy10.;
datalines;
1      1/2/13      10
1      1/9/13       5
1      1/31/13     10
2      2/6/13       8
2      2/17/13      6
2      2/28/13     13
3      3/13/13     20
3      3/20/13      2
3      3/31/13     15
4      4/3/13       5
4      4/30/13     40
;
run;
data have ;
set have;
by mon;
if first.mon then _x=0;
_x+x;
if last.mon then mnthy_x=_x;
drop _x;
run;
data temp;
set have(rename=(mnthy_x=_mnthy_x) where=(_mnthy_x is not missing));
mon=mon+1;
drop x date;
run;
data want;
merge have temp;
by mon;
mth_pct_change=(mnthy_x-_mnthy_x)/_mnthy_x;
drop _mnthy_x;
run;

```

Xia Keshan

🔒 This topic is solved and locked.