turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Data Manipulation-calculating lagged standard devi...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-17-2015 05:10 PM - last edited on 11-18-2015 09:55 AM by ChrisHemedinger

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 |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to katie80

11-17-2015 06:06 PM - edited 11-17-2015 06:08 PM

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;
```