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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.