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.
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,
The above code simply computes the average of the previous three values of variable NEW and ignores variable DATE for this calculation.
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,
The above code simply computes the average of the previous three values of variable NEW and ignores variable DATE for this calculation.
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.
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;
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));
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.