Computing moving average by group and year

[drip_]

Hello,

I have data in the following form (this is a sample, not the complete dataset):

Person        Month         Value     Group
1             1             3         2           
1             2             2         2
1             3             4         2           
1             4             7         2
1             5             5         2           
1             6             1         2
1             7             1         2           
1             8             5         2
1             9             3         2           
1             10             2         2
1             11             6         2           
1             12             3         2
2             1             4         2           
2             2             2         2
2             3             6         2           
2             4             7         2
2             5             9         2           
2             6             3         2
2             7             1         2           
2             8             5         2
2             9             6         2           
2             10             7         2
2             11             1         2           
2             12             2        2
3             1             7         1           
3             2             2         1
3             3             5         1           
3             4             5         1
3             5             6         1           
3             6             3         1
3             7             4         1           
3             8             7         1
3             9             9         1          
3             10             1         1
3             11             2         1           
3             12             2         1
4             1             2         1           
4             2             5         1
4             3             4         1           
4             4             6         1
4             5             8         1           
4             6             9         1
4             7             2         1           
4             8             1         1
4             9             3         1         
4             10             6         1
4             11             4         1           
4             12             4         1

What I would like to do is to calculate moving averages (mean and median) for the past four months for each group. The problem is that my identification variable is the "Person" variable, but I need the average for the "Group" variable.

The desired output would be something like this:

April:

Group 1 - Avg (Jan - April)

Group 2 - Avg (Jan - April)

May:

Group 1 - Avg (Feb - May)

Group 2 - Avg (Feb - May)

...

Thanks for any help or suggestions!

[ballardw]

Better provide a Year value if you want stuff grouped by year.

You say " for the past four months for each group". Past based on what date?

Do you have access to the SAS/ETS module? If so there are procedures that will do this fairly easily, especially if you provide actual DATE information.

Do you intend to include periods starting in Oct, Nov and/or Dec? Since they do not have 4 months within the given example there summaries potentially can be quite different,

[drip_]

Hi ballardw,

you're right - I made a mistake in the title. It should be by group and month.

The month variable is always a month-end variable, so for example 01/31/XX or 02/28/XX. Starting in April, I would like to collect data at that point in time plus the previous three months (so four months in total) and compute an average. From my point of view, it should be possible to do this from April thru December, since the previous four months are used - it would not be possible in Jan, Feb and March.

I do have the SAS/ETS module - which Proc can I use for this task?

[ballardw]

I can't provide code as I don't have the module for use. Proc Expand should work https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_syntax08.htm once the data is ready. Which mean sorting by the variable(s) you want as a By group (your group) and actual dates instead of a random month.

Look at the syntax for the Convert statement and then follow that to the transformations ( https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_details19.htm#etsug_expand... )  Note that there are Window (the previous 4 months) operators. I think that yours would look something like:

convert x=y / transformout=( movave 4 );

I can't test code with my system but I think you might get a start with.

data have;
   input Person        Month         Value     Group;
   date = mdy(month,1,2021);
   format date date9.;
datalines;
1             1             3         2           
1             2             2         2
1             3             4         2           
1             4             7         2
1             5             5         2           
1             6             1         2
1             7             1         2           
1             8             5         2
1             9             3         2           
1             10             2         2
1             11             6         2           
1             12             3         2
2             1             4         2           
2             2             2         2
2             3             6         2           
2             4             7         2
2             5             9         2           
2             6             3         2
2             7             1         2           
2             8             5         2
2             9             6         2           
2             10             7         2
2             11             1         2           
2             12             2        2
3             1             7         1           
3             2             2         1
3             3             5         1           
3             4             5         1
3             5             6         1           
3             6             3         1
3             7             4         1           
3             8             7         1
3             9             9         1          
3             10             1         1
3             11             2         1           
3             12             2         1
4             1             2         1           
4             2             5         1
4             3             4         1           
4             4             6         1
4             5             8         1           
4             6             9         1
4             7             2         1           
4             8             1         1
4             9             3         1         
4             10             6         1
4             11             4         1           
4             12             4         1
;

proc sort data=have;
   by group person;
run;

proc expand data=have out=want method=none
   align=beginning 
   from=month
   to=month4
   ;
   by group;
   id date;
   convert value=meanvalue / transformout=( movave 4 );
run;

ID  identifies which variable to use for the interval controls.

I'm not sure about the To being exactly what you want and may not be needed. So if your results are totally wonky play with that.

I would use this on your small example so it is possible to at least estimate if things are odd.

The moving average may have values when there are not complete intervals so a step may be needed to filter them.

[drip_]

Hi there - thanks a lot for your help!

Unfortunately, I ran into some problems with your code:

When I ran the code as you posted it, there seemed to be a problem with method=none and the three following lines.

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc expand data=have out=want method=none
 70            align=beginning
 71            from=month
 72            to=month4
 73            ;
 74            by group;
 75            id date;
 76            convert value=meanvalue / transformout=( movave 4 );
 77         run;
 
 ERROR: The option METHOD=NONE can only be used when no frequency conversion is specified by the TO= or FACTOR= option.
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
 WARNING: Data set WORK.WANT was not replaced because this step was stopped.

Because of this error message, I took the method = none part out of the code.
However, when I did that I ran into the following problem:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc expand data=have out=want
 70            align=beginning
 71            from=month
 72            to=month4
 73            ;
 74            by group;
 75            id date;
 76            convert value=meanvalue / transformout=( movave 4 );
 77         run;
 
 ERROR: The data set WORK.HAVE is not sorted by the ID variable. At observation number 13, date=01JAN2021, but date=01DEC2021 for 
        the previous observation.
 NOTE: The above message was for the following BY group:
       Group=1
 ERROR: The data set WORK.HAVE is not sorted by the ID variable. At observation number 37, date=01JAN2021, but date=01DEC2021 for 
        the previous observation.
 NOTE: The above message was for the following BY group:
       Group=2
 NOTE: There were 48 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 0 observations and 3 variables.

Because it said the dataset was not sorted by the ID variable, I tried ordering by date and group and ran the code again ( I receive the same error message when ordering by date and person):

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc sort data=have;
 70            by date group;
 71         run;
 72         
 73         proc expand data=have out=want
 74            align=beginning
 75            from=month
 76            to=month4
 77            ;
 78            by group;
 79            id date;
 80            convert value=meanvalue / transformout=( movave 4 );
 81         run;
 
 ERROR: Observation with duplicate ID value found. The value of the ID variable, date=01JAN2021, at observation number 2 in data set 
        WORK.HAVE is the same as the previous observation.
 NOTE: The above message was for the following BY group:
       Group=1
 ERROR: Observation with duplicate ID value found. The value of the ID variable, date=01JAN2021, at observation number 4 in data set 
        WORK.HAVE is the same as the previous observation.
 ERROR: Data set WORK.HAVE is not sorted in ascending sequence. The current BY group has Group = 2 and the next BY group has Group = 
        1.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: There were 5 observations read from the data set WORK.HAVE.
 WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
 WARNING: Data set WORK.WANT was not replaced because this step was stopped.

I also tried running the code with method=none instead of  from=month to=month4 part, but this also did not work.

First, ordered by date and group:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc expand data=have out=want method=none
 70            align=beginning;
 71            by group;
 72            id date;
 73            convert value=meanvalue / transformout=( movave 4 );
 74         run;
 
 ERROR: Observation with duplicate ID value found. The value of the ID variable, date=01JAN2021, at observation number 2 in data set 
        WORK.HAVE is the same as the previous observation.
 NOTE: The above message was for the following BY group:
       Group=1
 ERROR: Observation with duplicate ID value found. The value of the ID variable, date=01JAN2021, at observation number 4 in data set 
        WORK.HAVE is the same as the previous observation.
 ERROR: Data set WORK.HAVE is not sorted in ascending sequence. The current BY group has Group = 2 and the next BY group has Group = 
        1.
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
 WARNING: Data set WORK.WANT was not replaced because this step was stopped.
 NOTE: PROCEDURE EXPAND used (Total process time):

Second, ordered by group and person:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc expand data=have out=want method=none
 70            align=beginning;
 71            by group;
 72            id date;
 73            convert value=meanvalue / transformout=( movave 4 );
 74         run;
 
 ERROR: The data set WORK.HAVE is not sorted by the ID variable. At observation number 13, date=01JAN2021, but date=01DEC2021 for 
        the previous observation.
 NOTE: The above message was for the following BY group:
       Group=1
 ERROR: The data set WORK.HAVE is not sorted by the ID variable. At observation number 37, date=01JAN2021, but date=01DEC2021 for 
        the previous observation.
 NOTE: The above message was for the following BY group:
       Group=2
 NOTE: The data set WORK.WANT has 0 observations and 6 variables.

I feel like the problem is that I basically have two groups: person with 12 observations each and then the actual group consisting of the individual people.
Is there a way to solve this problem?

[Ksharp]

Assuming there is no gap between month.

data have;
   input Person        Month         Value     Group;
datalines;
1             1             3         2           
1             2             2         2
1             3             4         2           
1             4             7         2
1             5             5         2           
1             6             1         2
1             7             1         2           
1             8             5         2
1             9             3         2           
1             10             2         2
1             11             6         2           
1             12             3         2
2             1             4         2           
2             2             2         2
2             3             6         2           
2             4             7         2
2             5             9         2           
2             6             3         2
2             7             1         2           
2             8             5         2
2             9             6         2           
2             10             7         2
2             11             1         2           
2             12             2        2
3             1             7         1           
3             2             2         1
3             3             5         1           
3             4             5         1
3             5             6         1           
3             6             3         1
3             7             4         1           
3             8             7         1
3             9             9         1          
3             10             1         1
3             11             2         1           
3             12             2         1
4             1             2         1           
4             2             5         1
4             3             4         1           
4             4             6         1
4             5             8         1           
4             6             9         1
4             7             2         1           
4             8             1         1
4             9             3         1         
4             10             6         1
4             11             4         1           
4             12             4         1
;

data want ;
set have;
by group notsorted;
array temp{0:3} _temporary_;
if first.group then n=0;
n+1;
idx=mod(n,4);
temp{idx}=value;
if n>3 then do;
  moving_mean=mean(of temp{*});
  moving_median=median(of temp{*});
end;
drop n idx ;
run;

[drip_]

Thanks for your suggestion!

Your code works in so far as it does calculate a rolling average over the past four months.

This is the result I get:

Obs 	Person 	Month 	Value 	Group 	date 	moving_mean 	moving_median
1 	1 	1 	3 	2 	01JAN2021 	. 	.
2 	1 	2 	2 	2 	01FEB2021 	. 	.
3 	1 	3 	4 	2 	01MAR2021 	. 	.
4 	1 	4 	7 	2 	01APR2021 	4.00 	3.5
5 	1 	5 	5 	2 	01MAY2021 	4.50 	4.5
6 	1 	6 	1 	2 	01JUN2021 	4.25 	4.5
7 	1 	7 	1 	2 	01JUL2021 	3.50 	3.0
8 	1 	8 	5 	2 	01AUG2021 	3.00 	3.0
9 	1 	9 	3 	2 	01SEP2021 	2.50 	2.0
10 	1 	10 	2 	2 	01OCT2021 	2.75 	2.5
11 	1 	11 	6 	2 	01NOV2021 	4.00 	4.0
12 	1 	12 	3 	2 	01DEC2021 	3.50 	3.0
13 	2 	1 	4 	2 	01JAN2021 	3.75 	3.5
14 	2 	2 	2 	2 	01FEB2021 	3.75 	3.5
15 	2 	3 	6 	2 	01MAR2021 	3.75 	3.5
16 	2 	4 	7 	2 	01APR2021 	4.75 	5.0
17 	2 	5 	9 	2 	01MAY2021 	6.00 	6.5
18 	2 	6 	3 	2 	01JUN2021 	6.25 	6.5
19 	2 	7 	1 	2 	01JUL2021 	5.00 	5.0
20 	2 	8 	5 	2 	01AUG2021 	4.50 	4.0
21 	2 	9 	6 	2 	01SEP2021 	3.75 	4.0
22 	2 	10 	7 	2 	01OCT2021 	4.75 	5.5
23 	2 	11 	1 	2 	01NOV2021 	4.75 	5.5
24 	2 	12 	2 	2 	01DEC2021 	4.00 	4.0
25 	3 	1 	7 	1 	01JAN2021 	. 	.
26 	3 	2 	2 	1 	01FEB2021 	. 	.
27 	3 	3 	5 	1 	01MAR2021 	. 	.
28 	3 	4 	5 	1 	01APR2021 	4.75 	5.0
29 	3 	5 	6 	1 	01MAY2021 	4.50 	5.0
30 	3 	6 	3 	1 	01JUN2021 	4.75 	5.0
31 	3 	7 	4 	1 	01JUL2021 	4.50 	4.5
32 	3 	8 	7 	1 	01AUG2021 	5.00 	5.0
33 	3 	9 	9 	1 	01SEP2021 	5.75 	5.5
34 	3 	10 	1 	1 	01OCT2021 	5.25 	5.5
35 	3 	11 	2 	1 	01NOV2021 	4.75 	4.5
36 	3 	12 	2 	1 	01DEC2021 	3.50 	2.0
37 	4 	1 	2 	1 	01JAN2021 	1.75 	2.0
38 	4 	2 	5 	1 	01FEB2021 	2.75 	2.0
39 	4 	3 	4 	1 	01MAR2021 	3.25 	3.0
40 	4 	4 	6 	1 	01APR2021 	4.25 	4.5
41 	4 	5 	8 	1 	01MAY2021 	5.75 	5.5
42 	4 	6 	9 	1 	01JUN2021 	6.75 	7.0
43 	4 	7 	2 	1 	01JUL2021 	6.25 	7.0
44 	4 	8 	1 	1 	01AUG2021 	5.00 	5.0
45 	4 	9 	3 	1 	01SEP2021 	3.75 	2.5
46 	4 	10 	6 	1 	01OCT2021 	3.00 	2.5
47 	4 	11 	4 	1 	01NOV2021 	3.50 	3.5
48 	4 	12 	4 	1 	01DEC2021 	4.25 	4.0

As of right now, it calculates the moving values per person - what I need would be the average number per group.

For example, for April:

- Group 1:

Mean of (3, 2, 4, 7) and (4, 2, 6, 7) = 4.375

Median of (3, 2, 4, 7) and (4, 2, 6, 7) = 4

I suppose for the mean you could take the mean of the means (4 for person 1 and 4.75 for person 2): (4 + 4.75)/2 = 4.375 but I'm concerned about the median (3.5 for person 1 and 5 for person 2): would give a median of 4.25.

Is it possible to mend the code so that it calculates the averages per group rather than per person?

Another minor thing: as of right now, the moving averages are also calculated across persons, for example for person  2 and January the values from October, November and December for person 1 are used as well as the value from January from person 2.

[Ksharp]

OK. How about this one ?

data have;
   input Person        Month         Value     Group;
datalines;
1             1             3         2           
1             2             2         2
1             3             4         2           
1             4             7         2
1             5             5         2           
1             6             1         2
1             7             1         2           
1             8             5         2
1             9             3         2           
1             10             2         2
1             11             6         2           
1             12             3         2
2             1             4         2           
2             2             2         2
2             3             6         2           
2             4             7         2
2             5             9         2           
2             6             3         2
2             7             1         2           
2             8             5         2
2             9             6         2           
2             10             7         2
2             11             1         2           
2             12             2        2
3             1             7         1           
3             2             2         1
3             3             5         1           
3             4             5         1
3             5             6         1           
3             6             3         1
3             7             4         1           
3             8             7         1
3             9             9         1          
3             10             1         1
3             11             2         1           
3             12             2         1
4             1             2         1           
4             2             5         1
4             3             4         1           
4             4             6         1
4             5             8         1           
4             6             9         1
4             7             2         1           
4             8             1         1
4             9             3         1         
4             10             6         1
4             11             4         1           
4             12             4         1
;

proc sql;
create table want as 
select a.*,
(select case when count(distinct month)=4 then mean(value) else . end 
from have where group=a.group and month between a.month-3 and a.month) as moving_mean,
(select case when count(distinct month)=4 then median(value) else . end
from have where group=a.group and month between a.month-3 and a.month) as moving_median
 from (select * from (select distinct group from have),(select distinct month from have)) as a;
quit;

[Tom · Accepted solution]

So you want the average of VALUE across both four month and multiple "person"s?  

Will every person have data for all four months?

You can use a multilabel format.

data have;
  input Person  Month Value Group;
cards;
  1  1  3  2
  1  2  2  2
  1  3  4  2
  1  4  7  2
  1  5  5  2
  1  6  1  2
  1  7  1  2
  1  8  5  2
  1  9  3  2
  1 10  2  2
  1 11  6  2
  1 12  3  2
  2  1  4  2
  2  2  2  2
  2  3  6  2
  2  4  7  2
  2  5  9  2
  2  6  3  2
  2  7  1  2
  2  8  5  2
  2  9  6  2
  2 10  7  2
  2 11  1  2
  2 12  2  2
  3  1  7  1
  3  2  2  1
  3  3  5  1
  3  4  5  1
  3  5  6  1
  3  6  3  1
  3  7  4  1
  3  8  7  1
  3  9  9  1
  3 10  1  1
  3 11  2  1
  3 12  2  1
  4  1  2  1
  4  2  5  1
  4  3  4  1
  4  4  6  1
  4  5  8  1
  4  6  9  1
  4  7  2  1
  4  8  1  1
  4  9  3  1
  4 10  6  1
  4 11  4  1
  4 12  4  1
;

proc format ;
value rolling_month (multilabel)
  1-4 = '1 Jan-Apr'
  2-5 = '2 Feb-May'
  3-6 = '3 Mar-Jun'
  4-7 = '4 Apr-Jul'
  5-8 = '5 May-Aug'
  6-9 = '6 Jun-Sep'
  7-10= '7 Jul-Oct'
  8-11= '8 Aug-Nov'
  9-12= '9 Sep-Dec'
;
run;

proc means data=have nway mean median;
  class group ;
  class month / mlf;
  var value;
  format month rolling_month.;
run;

                   Analysis Variable : Value

                               N
       Group    Month        Obs            Mean          Median
----------------------------------------------------------------
           1    1 Jan-Apr      8       4.5000000       5.0000000

                2 Feb-May      8       5.1250000       5.0000000

                3 Mar-Jun      8       5.7500000       5.5000000

                4 Apr-Jul      8       5.3750000       5.5000000

                5 May-Aug      8       5.0000000       5.0000000

                6 Jun-Sep      8       4.7500000       3.5000000

                7 Jul-Oct      8       4.1250000       3.5000000

                8 Aug-Nov      8       4.1250000       3.5000000

                9 Sep-Dec      8       3.8750000       3.5000000

           2    1 Jan-Apr      8       4.3750000       4.0000000

                2 Feb-May      8       5.2500000       5.5000000

                3 Mar-Jun      8       5.2500000       5.5000000

                4 Apr-Jul      8       4.2500000       4.0000000

                5 May-Aug      8       3.7500000       4.0000000
...

[drip_]

Thanks this is perfect!

Is it necessary that every person has complete data? It should be the case with my data, but would still be good to know.

Thanks again, this was a great help

[Tom]

---

@drip_ wrote:

Thanks this is perfect!

Is it necessary that every person has complete data? It should be the case with my data, but would still be good to know.

Thanks again, this was a great help

---

If you have unbalanced data then take a simple mean might add more weight to the values for persons that provided more samples.  You might want to instead take the mean per person and then that the mean over the groups of persons.  Then a person with 2 samples in the time interval will have an equal impact as someone with 4 samples.

Talk with a Statistician to determine what is the best method for your situation.

[drip_]

Yes you are right. I asked more from a practical POV - whether the code would work. Still, that's an important point, I'll keep it in mind.
Thanks again!