## Data Manipulation-calculating lagged standard deviations

Occasional Contributor
Posts: 11

# Data Manipulation-calculating lagged standard deviations

[ Edited ]

Hi,

I have the following variables in columns B through E. I want to create variables in columns F and G.

Basically, I want to look at the standard deviation of past prices and returns for each id without including the current price and return.

Is there a way to do this? Thanks!!!

 A B C D E F G 2 id date price return sd(price) sd(return) 3 1a 2014 101 5 1.25 1.57 1.25= standard deviation of (D4: D8) 1.57= standard deviation of (E4: E8) 4 1a 2013 102 4 1.22 1.75 1.22= standard deviation of (D5: D8) 1.75= standard deviation of (E5: E8) 5 1a 2012 99.5 5.5 1.26 2.08 1.26= standard deviation of (D6: D8) 2.08= standard deviation of (E6: E8) 6 1a 2011 101 3 1.77 2.12 1.77= standard deviation of (D7: D8) 2.12= standard deviation of (E7: E8) 7 1a 2010 102 7 - - missing since standard deviation cannot be calculated with one obs 8 1a 2009 99.5 4 - - missing since standard deviation cannot be calculated with zero obs 9 1b 2014 101 5.5 1.25 1.52 1.25= standard deviation of (D10: D14) 1.57= standard deviation of (E4: E14) 10 1b 2013 102 3 1.22 1.41 1.22= standard deviation of (D11: D14) 1.75= standard deviation of (E11: E14) 11 1b 2012 99.5 7 1.26 0.58 1.26= standard deviation of (D12: D14) 2.08= standard deviation of (E12: E14) 12 1b 2011 101 4 1.77 0.71 1.77= standard deviation of (D1314) 2.12= standard deviation of (E13: E14) 13 1b 2010 102 5 - - missing since standard deviation cannot be calculated with one obs 14 1b 2009 99.5 4 - - missing since standard deviation cannot be calculated with zero obs
Super User
Posts: 19,770

## Re: Data Manipulation-calculating lagged standard deviations

[ Edited ]

Do you have SAS/ETS? If so, look into Proc EXPAND.

Otherwise here's an example of calculating min/max with a temporary array that could be expanded for your situation.

``````/*
How to calculate a moving min/max with a window of 4.
Resets by group ID
https://communities.sas.com/message/244232
Courtesy of PGStats
*/

data want;
array p{0:3} _temporary_;
set have; by object;
if first.object then call missing(of p{*});
p{mod(_n_,4)} = price;
lowest = min(of p{*});
highest = max(of p{*});
run;``````
Discussion stats