topic Re: SAS_Mean and Standard deviation of previous and following n rows by group in SAS Programming

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

Khaladdin — Fri, 25 Oct 2019 08:15:35 GMT

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.

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

PeterClemmensen — Fri, 25 Oct 2019 08:40:51 GMT

Do you have a SAS/ETS license?

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

Khaladdin — Fri, 25 Oct 2019 08:44:56 GMT

Yes, I have

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

PeterClemmensen — Fri, 25 Oct 2019 09:14:18 GMT

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

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

Khaladdin — Fri, 25 Oct 2019 10:01:13 GMT

Great! It works well. Many thanks!

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

PeterClemmensen — Fri, 25 Oct 2019 10:14:33 GMT

Anytime 🙂