Calculating average

[arjinram]

Hi,

Need help in caliculating average of last  months from the current month, sample data is as below, pls help.

      Date             New   Return Rework                         New_app

01Mar2014        100     70           30 

01Apr2014         125     80           40 

01May2014        150     90           50 

01Jun2014         175    100          60            average(01mar2014,01apr2014,01may2014)=125 {average of last 3 months)

01Jul2014           200    110         70            average(01apr2014,01may2014,01jun2014)=150

01Aug2014         225    120         80            average(01may2014,01jun2014,01jul2015)=175

01Sep2014         250    130         90            average(01jun2014,01jul2015,01aug2015)=200

Thanks in advance.

[FreelanceReinh · Accepted solution]

Hi @arjinram,

Maybe this could be a start:

/* Create test data */
data have;
input Date :date. New Return Rework;
format date date9.;
cards;
01Mar2014 100 70 30 
01Apr2014 125 80 40 
01May2014 150 90 50 
01Jun2014 175 100 60 
01Jul2014 200 110 70 
01Aug2014 225 120 80 
01Sep2014 250 130 90 
;

proc sort data=have;
by date;
run; /* This requires SAS date values in variable DATE! */

/* Create variable New_app */
data want;
set have;
array v[3] _temporary_;
if n(of v[*])=3 then New_app=mean(of v[*]);
v[mod(_n_,3)+1]=new;
run;

proc print data=want;
run;

Probably, we will need to adapt this code to the requirements of your real data, if these may involve, for example,

• BY groups
• gaps > 1 month between two successive dates
• missing values of variable NEW

The above code simply computes the average of the previous three values of variable NEW and ignores variable DATE for this calculation.

[FreelanceReinh]

Here is another approach:

proc sql;
create table want as
select a.*, (select case when count(new)=3 then mean(new) else . end
             from have 
             where 1<=intck('month', date, a.date)<=3)
            as New_app
from have a;
quit;

Unlike my previous suggestion, this approach computes the average based on the date values: Those values of variable NEW are included in the calculation whose corresponding DATE values are 1, 2 or 3 months prior to the DATE value of the current observation. If this calculation involves fewer than 3 non-missing values, New_app will be left missing. (This is the case for the first three observations.) It is not anticipated that more than 3 observations could satisfy the date condition.

[ballardw]

And using @FreelanceReinh's sorted Have set, here is another approach:

data want;
   set have;
   New_app= mean(lag1(new),lag2(new),lag3(new));
   if _n_<4 then new_app=.;
run;

[Rick_SAS]

If you have SAS/ETS software, you can compute a three-month moving average by using PROC EXPAND and then merge the data and the average.

By the way, the usual way to compute a three-month rolling average is to associate the rolling mean with the CURRENT month and the two PREVIOUS months. To use @ballardw's notation:

   New_app= mean(new,lag1(new),lag2(new));