## Calculating average

Solved
Occasional Contributor
Posts: 5

# Calculating average

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

Accepted Solutions
Solution
‎03-07-2016 07:28 PM
Posts: 1,259

## Re: Calculating average

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.

All Replies
Solution
‎03-07-2016 07:28 PM
Posts: 1,259

## Re: Calculating average

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.

Posts: 1,259

## Re: Calculating average

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.

Super User
Posts: 13,583

## Re: Calculating average

And using @FreelanceReinhard'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;``````
SAS Super FREQ
Posts: 4,247

## Re: Calculating average

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));

🔒 This topic is solved and locked.