SAS_Mean and Standard deviation of previous and following n rows by group

[Khaladdin]

Hi all,

I want to compute the mean and standard deviation of previous and following n rows by group. For example,

I have:

     Row Number                Price

            1                              10

            2                               5

            3                              11

            4                              12

            5                              15

            6                              18

I want to compute the mean and standard deviations of the previous and following 2 rows by group. So, I want

     Row Number                Price                Mean                                                                                    Stddev

            1                              10                  (5+11)/2  (I do not have any previous row)                           Same method but for stan.deviatin

            2                               5                   (10+11+12)/3 (I have only one previous row)

            3                              11                  (10+5+12+15)/4 (I have previous and following two rows)

            4                              12                  (5+11+15+18)/4 (I have previous and following two rows)

            5                              15                  (11+12+18)/3 (I have only one following row)

            6                              18                  (12+15)/2 (I do not have any following row)

I show the sample without any group for simplicity. I should do all the computations by group.

[PeterClemmensen]

Do you have a SAS/ETS license?

[Khaladdin]

Yes, I have

[PeterClemmensen · Accepted solution]

Here is one way. I added a few more groups to your data to demonstrate that it handles by groups as well.

data have;
input Group Price;
datalines;
1 10
1 5
1 11
1 12
1 15
1 18
2 10
2 5
2 11
2 12
2 15
2 18
3 10
3 5
3 11
3 12
3 15
3 18
;

data temp / view=temp;
   do t=1 by 1 until (last.Group);
      set have;
      by Group;
      output;
   end;
run;

proc expand data=temp out=want method=none;
   by Group;
   id t;
   convert Price=Mean   / transformout=( cmovave (1 1 0 1 1));
   convert Price=Stddev / transformout=( cmovstd (1 1 0 1 1));
   format Mean Stddev 8.2;
run;

Result:

Group  t   Mean    Stddev Price 
1      1   8.00    2.12   10 
1      2   11.00   0.47   5 
1      3   10.50   1.82   11 
1      4   12.25   2.43   12 
1      5   13.67   1.78   15 
1      6   13.50   1.06   18 
2      1   8.00    2.12   10 
2      2   11.00   0.47   5 
2      3   10.50   1.82   11 
2      4   12.25   2.43   12 
2      5   13.67   1.78   15 
2      6   13.50   1.06   18 
3      1   8.00    2.12   10 
3      2   11.00   0.47   5 
3      3   10.50   1.82   11 
3      4   12.25   2.43   12 
3      5   13.67   1.78   15 
3      6   13.50   1.06   18 

[Khaladdin]

Great! It works well. Many thanks!

[PeterClemmensen]

Anytime 🙂