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.
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
Do you have a SAS/ETS license?
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
Anytime 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.