topic Re: Calculating Monthly Percent change in SAS Programming

Calculating Monthly Percent change

NewSASPerson — Wed, 28 May 2014 23:33:46 GMT

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

Re: Calculating Monthly Percent change

Reeza — Thu, 29 May 2014 00:17:57 GMT

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

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

Re: Calculating Monthly Percent change

NewSASPerson — Thu, 29 May 2014 00:39:37 GMT

yes to both questions.

Re: Calculating Monthly Percent change

Reeza — Thu, 29 May 2014 00:51:09 GMT

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

Worth reading up on.

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;

Re: Calculating Monthly Percent change

PGStats — Thu, 29 May 2014 02:28:52 GMT

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.

Re: Calculating Monthly Percent change

Ksharp — Thu, 29 May 2014 14:24:20 GMT

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