BookmarkSubscribeRSS Feed
ivanpersie
Fluorite | Level 6

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?

tickerdateReturn
MC:AFI01/02/20120.119293
MC:AFI01/03/2012-0.04931
MC:AFI02/04/20120.003213
MC:AFI01/05/20120.058013
MC:AFI01/06/20120.081692
MC:AFI02/07/2012-0.01738
MC:AFI01/08/20120.031073
MC:AFI03/09/2012-0.04876
MC:AFI01/10/2012-7.7E-18
MC:AFI01/11/2012-0.00358
MC:AFI03/12/2012-1.7E-18
MC:AFI01/01/2013-0.01807
MC:AFI01/02/2013-0.01294
MC:AFI01/03/20130.016577
MC:AFI01/04/2013-3.4E-19
MC:AFI01/05/2013-0.01843
MC:AFI03/06/20130.071997
MC:AFI01/07/2013-0.01888
MC:AFI01/08/20130.000167
MC:AFI02/09/2013-0.01898
MC:AFI01/10/20130.030281
MC:AFI01/11/2013-0.0113
MC:AFI02/12/20130.062201
MC:AFI01/01/2014-0.03606
MC:AFI03/02/2014-0.00744
MC:AFI03/03/20140.027423
MC:AFI01/04/20140.028399
MC:AFI01/05/20140.049554
MC:AFI02/06/20140.021179
MC:ALM02/11/1998-0.06051
MC:ALM01/12/19980.086504
MC:ALM01/01/1999-0.08341
MC:ALM01/02/1999-0.0093
MC:ALM01/03/1999-0.01582
MC:ALM01/04/1999-0.04226
MC:ALM03/05/19990.003297
MC:ALM01/06/19990.026105
MC:ALM01/07/19990.094456
MC:ALM02/08/19990.03068
MC:ALM01/09/1999-0.00608
MC:ALM01/10/19990.006082
MC:ALM01/11/19995.25E-19
MC:ALM01/12/1999-0.03381
MC:ALM03/01/2000-6.9E-18
MC:ALM01/02/2000-0.07148
MC:ALM01/03/20000.105293
MC:ALM03/04/2000-0.0544
MC:ALM01/05/20000.03913
MC:ALM01/06/2000-0.05202
MC:ALM03/07/2000-0.02296
MC:ALM01/08/20000.076617
MC:ALM01/09/20000.040158
MC:ALM02/10/2000-0.00632
MC:ALM01/11/2000-0.12292
MC:ALM01/12/20000.126085
MC:ALM01/01/2001-0.07033
MC:ALM01/02/20010.081329
MC:ALM01/03/20010.11498
MC:ALM02/04/2001-0.12282
MC:ALM01/05/20010.020262
MC:ALM01/06/2001-0.09375
MC:ALM02/07/20010.008857
MC:ALM01/08/20010.001888
MC:ALM03/09/2001-0.06694
MC:ALM01/10/2001-0.1578
MC:ALM01/11/20010.114995
MC:ALM03/12/2001-0.07123
MC:ALM01/01/2002-0.0996
MC:ALM01/02/20027.54E-18
MC:ALM01/03/2002-0.12672
MC:ALM01/04/2002-0.05835
MC:ALM01/05/20020.039278
MC:ALM03/06/20020.005442
MC:ALM01/07/2002-0.00833
MC:ALM01/08/20020.066769
MC:ALM02/09/20020.017166
MC:ALM01/10/20020.065583
MC:ALM01/11/2002-0.00793
MC:ALM02/12/2002-0.04925
MC:ALM01/01/20030.113764
MC:ALM03/02/20030.106617
MC:ALM03/03/20034.69E-18
MC:ALM01/04/20030.03095
MC:ALM01/05/20030.120911
MC:ALM02/06/20030.070482
MC:ALM01/07/20030.040185
MC:ALM01/08/20030.073979
MC:ALM01/09/20030.0591
MC:ALM01/10/2003-4E-18
MC:ALM03/11/20030.016197
MC:ALM01/12/2003-0.01129
MC:ALM01/01/20040.14689
MC:ALM02/02/20040.141561
MC:ALM01/03/20040.014553
MC:ALM01/04/20040.133983
MC:ALM03/05/2004-0.04194
MC:ALM01/06/20040.00085
MC:ALM01/07/20040.032334
MC:ALM02/08/20040.006762
MC:ALM01/09/2004-0.11341
MC:ALM01/10/2004-0.07478
MC:ALM01/11/20040.021581
MC:ALM01/12/20040.070716
MC:ALM03/01/20050.037824
MC:ALM01/02/20050.056945
MC:ALM01/03/20050.030057
MC:ALM01/04/2005-0.02669
MC:ALM02/05/20050.024505
MC:ALM01/06/20050.002185
MC:ALM01/07/20050.066808
MC:ALM01/08/20050.04384
MC:ALM01/09/2005-0.02118
MC:ALM03/10/20050.045959
MC:ALM01/11/20050.059406
MC:ALM01/12/20050.026583
MC:ALM02/01/20060.241707
MC:ALM01/02/20060.027552
MC:ALM01/03/2006-0.0079
MC:ALM03/04/20060.038869
3 REPLIES 3
AskoLötjönen
Quartz | Level 8

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;

ivanpersie
Fluorite | Level 6

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.

AskoLötjönen
Quartz | Level 8

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 562 views
  • 0 likes
  • 2 in conversation