Hi all,
I have a dataset about firm stock monthly returns that looks like the following:
firmID date return fyr
1 20100131 0.0003 9
1 20100228 0.0001 9
.......................
1 20101231 0.0012 9
1 20110131 -0.0015 9
.........................
2 20110430 -0.1201 12
2 20110531 -0.0008 12
2 20110630 0.0154 12
...........................
2 20151231 0.0087 12
3 19960531 0.0789 5
3 19960630 0.1023 5
...............................
3 20141231 -0.1234 5
I have over one-thousand firms, and these firms may have data up to 30 years. I have one return for each pair of firm-date, and these returns are monthly. fyr is the fiscal year-end. I want to calculate standard deviation for monthly return over the past fiscal year. In other words:
For firm 1, I want to calculate:
2010: standard deviation of (2009 Oct. return, 2009 Nov. return, 2009 Dec. return, 2010 Jan. return, ....., 2010 Sept. return)
2011: standard deviation of (2010 Oct. return, 2010 Nov. return, 2010 Dec. return, 2011 Jan. return, ....., 2011 Sept. return)
......
For firm 2, I want to calculate:
2011: standard deviation of (2011 Jan. return, 2011 Feb. return, 2011 Mar. return, ....., 2011 Dec. return)
2012
2013
..........
For firm 3, I want to calculate:
1996: standard deviation of (1995 Jun. return, 1995 Jul. return, ...., 1995 Dec. return, 1996, jan. return, ....., 1996 Apr. return)
1997
1998
..............
So the output will look something like this:
firmID year std_ret
1 2010 0.20394
1 2011 1.23324
............
2 2011 0.234234
2 2012 1.234
.............
2 2015 3.34732
3 1996 2.34234
..............
3 2014 1.2345
I might have missing values on return. So I might not have the full 12-month data on each pair of firm-year. I want to restrict SAS to calculate only if I have at least 7 months out of 12 months. If there is not sufficient data, then SAS should drop this firm-year.
This is not a rolling standard deviation as I only use each line once. However for different firms, the calculation will be different as I start and end at a different month.
Please help! This seems difficult for me. Any suggestion is greatly appreicated. Thank you!
... View more