Data Manipulation-calculating lagged standard deviations

katie80 — Wed, 18 Nov 2015 14:55:48 GMT

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 (D13:D14)	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

Re: Data Manipulation-calculating lagged standard deviations

Reeza — Tue, 17 Nov 2015 23:08:45 GMT

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;

topic Data Manipulation-calculating lagged standard deviations in SAS Data Management

Data Manipulation-calculating lagged standard deviations

Re: Data Manipulation-calculating lagged standard deviations