Hi guys,
I have stock returns data for so many stocks/Tickers. Out of these returns, i want to compute standard deviation using trailing twelve months returns. What code can i use to get this?
ticker | date | Return |
MC:AFI | 01/02/2012 | 0.119293 |
MC:AFI | 01/03/2012 | -0.04931 |
MC:AFI | 02/04/2012 | 0.003213 |
MC:AFI | 01/05/2012 | 0.058013 |
MC:AFI | 01/06/2012 | 0.081692 |
MC:AFI | 02/07/2012 | -0.01738 |
MC:AFI | 01/08/2012 | 0.031073 |
MC:AFI | 03/09/2012 | -0.04876 |
MC:AFI | 01/10/2012 | -7.7E-18 |
MC:AFI | 01/11/2012 | -0.00358 |
MC:AFI | 03/12/2012 | -1.7E-18 |
MC:AFI | 01/01/2013 | -0.01807 |
MC:AFI | 01/02/2013 | -0.01294 |
MC:AFI | 01/03/2013 | 0.016577 |
MC:AFI | 01/04/2013 | -3.4E-19 |
MC:AFI | 01/05/2013 | -0.01843 |
MC:AFI | 03/06/2013 | 0.071997 |
MC:AFI | 01/07/2013 | -0.01888 |
MC:AFI | 01/08/2013 | 0.000167 |
MC:AFI | 02/09/2013 | -0.01898 |
MC:AFI | 01/10/2013 | 0.030281 |
MC:AFI | 01/11/2013 | -0.0113 |
MC:AFI | 02/12/2013 | 0.062201 |
MC:AFI | 01/01/2014 | -0.03606 |
MC:AFI | 03/02/2014 | -0.00744 |
MC:AFI | 03/03/2014 | 0.027423 |
MC:AFI | 01/04/2014 | 0.028399 |
MC:AFI | 01/05/2014 | 0.049554 |
MC:AFI | 02/06/2014 | 0.021179 |
MC:ALM | 02/11/1998 | -0.06051 |
MC:ALM | 01/12/1998 | 0.086504 |
MC:ALM | 01/01/1999 | -0.08341 |
MC:ALM | 01/02/1999 | -0.0093 |
MC:ALM | 01/03/1999 | -0.01582 |
MC:ALM | 01/04/1999 | -0.04226 |
MC:ALM | 03/05/1999 | 0.003297 |
MC:ALM | 01/06/1999 | 0.026105 |
MC:ALM | 01/07/1999 | 0.094456 |
MC:ALM | 02/08/1999 | 0.03068 |
MC:ALM | 01/09/1999 | -0.00608 |
MC:ALM | 01/10/1999 | 0.006082 |
MC:ALM | 01/11/1999 | 5.25E-19 |
MC:ALM | 01/12/1999 | -0.03381 |
MC:ALM | 03/01/2000 | -6.9E-18 |
MC:ALM | 01/02/2000 | -0.07148 |
MC:ALM | 01/03/2000 | 0.105293 |
MC:ALM | 03/04/2000 | -0.0544 |
MC:ALM | 01/05/2000 | 0.03913 |
MC:ALM | 01/06/2000 | -0.05202 |
MC:ALM | 03/07/2000 | -0.02296 |
MC:ALM | 01/08/2000 | 0.076617 |
MC:ALM | 01/09/2000 | 0.040158 |
MC:ALM | 02/10/2000 | -0.00632 |
MC:ALM | 01/11/2000 | -0.12292 |
MC:ALM | 01/12/2000 | 0.126085 |
MC:ALM | 01/01/2001 | -0.07033 |
MC:ALM | 01/02/2001 | 0.081329 |
MC:ALM | 01/03/2001 | 0.11498 |
MC:ALM | 02/04/2001 | -0.12282 |
MC:ALM | 01/05/2001 | 0.020262 |
MC:ALM | 01/06/2001 | -0.09375 |
MC:ALM | 02/07/2001 | 0.008857 |
MC:ALM | 01/08/2001 | 0.001888 |
MC:ALM | 03/09/2001 | -0.06694 |
MC:ALM | 01/10/2001 | -0.1578 |
MC:ALM | 01/11/2001 | 0.114995 |
MC:ALM | 03/12/2001 | -0.07123 |
MC:ALM | 01/01/2002 | -0.0996 |
MC:ALM | 01/02/2002 | 7.54E-18 |
MC:ALM | 01/03/2002 | -0.12672 |
MC:ALM | 01/04/2002 | -0.05835 |
MC:ALM | 01/05/2002 | 0.039278 |
MC:ALM | 03/06/2002 | 0.005442 |
MC:ALM | 01/07/2002 | -0.00833 |
MC:ALM | 01/08/2002 | 0.066769 |
MC:ALM | 02/09/2002 | 0.017166 |
MC:ALM | 01/10/2002 | 0.065583 |
MC:ALM | 01/11/2002 | -0.00793 |
MC:ALM | 02/12/2002 | -0.04925 |
MC:ALM | 01/01/2003 | 0.113764 |
MC:ALM | 03/02/2003 | 0.106617 |
MC:ALM | 03/03/2003 | 4.69E-18 |
MC:ALM | 01/04/2003 | 0.03095 |
MC:ALM | 01/05/2003 | 0.120911 |
MC:ALM | 02/06/2003 | 0.070482 |
MC:ALM | 01/07/2003 | 0.040185 |
MC:ALM | 01/08/2003 | 0.073979 |
MC:ALM | 01/09/2003 | 0.0591 |
MC:ALM | 01/10/2003 | -4E-18 |
MC:ALM | 03/11/2003 | 0.016197 |
MC:ALM | 01/12/2003 | -0.01129 |
MC:ALM | 01/01/2004 | 0.14689 |
MC:ALM | 02/02/2004 | 0.141561 |
MC:ALM | 01/03/2004 | 0.014553 |
MC:ALM | 01/04/2004 | 0.133983 |
MC:ALM | 03/05/2004 | -0.04194 |
MC:ALM | 01/06/2004 | 0.00085 |
MC:ALM | 01/07/2004 | 0.032334 |
MC:ALM | 02/08/2004 | 0.006762 |
MC:ALM | 01/09/2004 | -0.11341 |
MC:ALM | 01/10/2004 | -0.07478 |
MC:ALM | 01/11/2004 | 0.021581 |
MC:ALM | 01/12/2004 | 0.070716 |
MC:ALM | 03/01/2005 | 0.037824 |
MC:ALM | 01/02/2005 | 0.056945 |
MC:ALM | 01/03/2005 | 0.030057 |
MC:ALM | 01/04/2005 | -0.02669 |
MC:ALM | 02/05/2005 | 0.024505 |
MC:ALM | 01/06/2005 | 0.002185 |
MC:ALM | 01/07/2005 | 0.066808 |
MC:ALM | 01/08/2005 | 0.04384 |
MC:ALM | 01/09/2005 | -0.02118 |
MC:ALM | 03/10/2005 | 0.045959 |
MC:ALM | 01/11/2005 | 0.059406 |
MC:ALM | 01/12/2005 | 0.026583 |
MC:ALM | 02/01/2006 | 0.241707 |
MC:ALM | 01/02/2006 | 0.027552 |
MC:ALM | 01/03/2006 | -0.0079 |
MC:ALM | 03/04/2006 | 0.038869 |
I added also other calculated statistics just to check that program is working as supposed:
proc sql;
create table want as
select h1.ticker,
h1.date,
std(h2.return) as return_Std,
mean(h2.return) as return_mean,
min(h2.return) as return_min,
max(h2.return) as return_max,
n(h2.return) as return_n
from have h1 ,
have h2
where h1.ticker = h2.ticker
and h2.date ge h1.date
and h1.date ge h2.date-365
group by h1.ticker, h1.date;
quit;
Hi. i dont get the same result when i cross check in Excel sheet. I want to use the past 12 months returns to compute the standard deviation. So for each month the trailing standard deviation = S.D of previous 12 months.
Sorry that program calculated 12 forthcoming months:
This should calculate 12 previous:
proc sql;
create table want as
select h1.ticker,
h1.date,
std(h2.return) as return_Std,
mean(h2.return) as return_mean,
min(h2.return) as return_min,
max(h2.return) as return_max,
n(h2.return) as return_n
from have h1 ,
have h2
where h1.ticker = h2.ticker
and h1.date ge h2.date
and h1.date le h2.date+365.25
group by h1.ticker, h1.date;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.