SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Data Manipulation-calculating lagged standard deviations

Reply
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 (D13Smiley Very Happy14)  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;
Ask a Question
Discussion stats
  • 1 reply
  • 225 views
  • 0 likes
  • 2 in conversation