DATA Step, Macro, Functions and more

Calculating average

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

 

 

Thanks in advance.

 


Accepted Solutions
Solution
‎03-07-2016 07:28 PM
Trusted Advisor
Posts: 1,118

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.

View solution in original post


All Replies
Solution
‎03-07-2016 07:28 PM
Trusted Advisor
Posts: 1,118

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.

Trusted Advisor
Posts: 1,118

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: 11,343

Re: Calculating average

Posted in reply to FreelanceReinhard

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: 3,755

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 296 views
  • 4 likes
  • 4 in conversation