Calculating Monthly Percent change

[NewSASPerson]

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

[Reeza]

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

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

[NewSASPerson]

yes to both questions.

[Reeza]

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;

[PGStats]

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

[Ksharp · Accepted solution]

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