topic Re: Calculating average in SAS Programming

Calculating average

arjinram — Sun, 06 Mar 2016 13:35:36 GMT

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.

Re: Calculating average

FreelanceReinh — Sun, 06 Mar 2016 14:24:21 GMT

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.

Re: Calculating average

FreelanceReinh — Sun, 06 Mar 2016 15:16:52 GMT

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.

Re: Calculating average

ballardw — Mon, 07 Mar 2016 16:49:56 GMT

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;

Re: Calculating average

Rick_SAS — Mon, 07 Mar 2016 18:08:29 GMT

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