## Calculate moving average by group

Occasional Contributor
Posts: 9

# Calculate moving average by group

I was trying to calculate 12 month moving average for each individual clients. I have the following simplified data in a table named individual_moving_avg with various users, dates and their incident count:

 SAP OpenDate Count 100004 Aug-13 30 100004 Sep-13 49 100004 Oct-13 42 100004 Nov-13 44 100004 Dec-13 37 … … … 100005 Aug-13 28 100005 Sep-13 17 100005 Oct-13 29 100005 Nov-13 15 100005 Dec-13 13 … … … 100007 Aug-13 9 100007 Sep-13 12 100007 Oct-13 14 100007 Nov-13 16 100007 Dec-13 23 … … … 100011 Aug-13 3 100011 Sep-13 3 100011 Oct-13 3 100011 Nov-13 0 100011 Dec-13 3 … … … 100012 Aug-13 0 100012 Sep-13 1 100012 Oct-13 1 100012 Nov-13 1 100012 Dec-13 3

...

Below is my code:

DATA Individual_moving_avg;

SET All_new;

BY SAP OpenDate;

length SAP_Recs 3

Average 8;

retain SAP_Recs;

If first.SAP=1 then SAP_Recs=0;

SAP_Recs= SAP_Recs+1;

if SAP_Recs>11 then do;

Average=(lag11(Count)+lag10(Count)+lag9(Count)+lag8(Count)+lag7(Count)+lag6(Count)+lag5(Count)+

lag4(Count)+lag3(Count)+lag2(Count)+lag(Count)+Count)/12;

end;

drop SAP_Recs;

RUN;

However, my result does not come out accurately. My correct count of 12 mo moving average does not show until the 23th month. Can anyone possibly help me out on this? Thanks.

Super User
Posts: 23,676

## Re: Calculate moving average by group

1. If you have SAS/ETS you can use Proc Expand

``````proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

proc expand data=stocks out=stocks2;
id date;
by stock;
convert open=open_avg/ transformout=(movave 12 trimleft 11);
run;

proc print data=stocks2 (obs=20);
run;``````

2. Or you can use an array method with Base SAS

``````proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data want;
set stocks;
by stock;
retain pre1-pre12;
array pre(12);

if first.stock then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, 12)+1;
pre(index)=open;

if count>=12 then
moving_average=mean(of pre(*));
drop count index pre1-pre12;
run;``````
Occasional Contributor
Posts: 9

## Re: Calculate moving average by group

Thank you for the code, Reez. Unfortunately I don thave SAS/ETS and can only use data step. I tried your array metho but it still didn't give me the right result. It that becuase there are gaps between my each ID?

Contributor
Posts: 63

## Re: Calculate moving average by group

Hi Reeza,

I want to calcultae moving avg for last 30 days from the current day,

how i can implement your given code.

Thanks for the help in advance.

Regards,

Kaushal

Super User
Posts: 23,676

Super User
Posts: 10,766

## Re: Calculate moving average by group

```Do you have a big table ?
If it was ,try Reeza's code .

NOTE: Reeza's code would work only if there are not gap within each ID .

```
Occasional Contributor
Posts: 9

## Re: Calculate moving average by group

Hi Ksharp,

Yes I do have a large table. It's a 3*117k table orignally. I tried Reeza's array but didn't work. Any suggestions? Thanks.

Super User
Posts: 23,676

## Re: Calculate moving average by group

Less than a million isn't a big table. The array method should work fine, please post your non working code.

Occasional Contributor
Posts: 9

## Re: Calculate moving average by group

Hi Reeza,

My three vairalbes are:

 SAP OpenDate Count

I'm counting the 12-moving average for count by SAP ID. Below is my array code:

data Individual_moving_avg;
set All_new2;
by SAP;
retain pre1-pre12;
array pre(12);

if first.SAP then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, 12)+1;
pre(index)=OpenDate;

if count>=12 then
moving_average=mean(of pre(*));
drop count index pre1-pre12;
run;

Thanks!

Occasional Contributor
Posts: 9

## Re: Calculate moving average by group

In addition, there are gaps between my SAP ID. They are not countining number.

Super User
Posts: 23,676

## Re: Calculate moving average by group

Unless you want the average of your dates this line is your biggest issue:

``pre(index)=OpenDate;``

Assign the value you want to average.

``pre(index)=Count;``

Because you're looking at month, you could put the month in. You'd have to test it though, not sure how it work with missing months.

``pre(month(openDate))=Count;``

Otherwise look at proc timeseries or a similar proc to add in your missing data points if you need to average over 12 months with missing dates. You need to decide how to account for it as well, if missing is it an average over 11 months or less, basically available data? Or do you need to modify it in some manner.

Super User
Posts: 23,676

## Re: Calculate moving average by group

I'm not sure what you mean by gaps in SAP ID. Each ID is its own, it doesn't matter. The months/dates need to be continuous.

If you're missing months is where you'll run into issues.

Super User
Posts: 10,766

## Re: Calculate moving average by group

```Reeza, Yes. I mean gap is missing month .

If you have big table try Hash Table:

data have;
do sap=1 to 100;
do year=1990 to 2016;
do month=1 to 12;
opendate=mdy(month,1,year);
count=ceil(100*ranuni(1234));
output;
end;
end;
end;
format opendate monyy7.;
drop year month;
run;

data want;
if _n_=1 then do;
if 0 then set have(rename=(count=_count));
declare hash h(dataset:'have(rename=(count=_count))',hashexp:20);
h.definekey('sap','opendate');
h.definedata('_count');
h.definedone();
end;
set have;
sum=0;n=0;
do i=intnx('month',opendate,-11) to opendate ;
if h.find(key:sap,key:i)=0 then do;
sum+_count; n+1;
end;
end;
mean=sum/n;
drop i sum n _count;
run;

```
Discussion stats
• 12 replies
• 3580 views
• 1 like
• 4 in conversation