standard deviation at the end of each month based on daily returns from past 12 months

[MAC1430]

Dear all,

I want to estimate volatility (standard deviation) at the end of each month based on daily returns from past 12 months, i.e., from 02 June 1987 to 27 May 1998.  The first volatility will be estimated at the end of May 1988, then at the end of June 1988, July 1988 and so on. I have a large data set, so will appreciate an efficient code.

data have;
infile cards expandtabs truncover;
input	date : yymmdd10. ret;
format date yymmdd10.;
cards;
19870602	0.001608579
19870603	0.003747323
19870626	0.020320856
19870702	0.014225501
19870703	0.004155844
19870727	-0.014300306
19870728	0.024870466
19870803	0.045931103
19870827	0.009944751
19870828	-0.004376368
19870902	-0.001627781
19870903	-0.011956522
19870928	0.002688172
19871002	0.008108108
19871027	0.002666667
19871028	0.002659574
19871102	-0.009042553
19871103	-0.013419216
19871125	-0.008412787
19871127	0.002820079
19871202	0.002832861
19871203	0.01299435
19871228	-0.005847953
19880125	-0.01497006
19880127	-0.019452888
19880128	-0.001859888
19880202	0.004968944
19880203	-0.001854141
19880225	-0.03257329
19880302	-0.026798307
19880303	0.014492754
19880325	-0.012779553
19880328	0.006472492
19880425	-0.001166181
19880427	0.012805588
19880428	-0.029885057
19880502	-0.039156627
19880503	0.00815047
19880525	-0.007361963
19880527	0.004326329
19880602	0
19880603	0.007348438
19880627	-0.014521452
19880628	-0.006697924
19880725	-0.001895136
19880727	0.004551365
19880728	0.01618123
19880802	-0.016025641
19880803	-0.022801303
19880825	-0.014814815
19880902	-0.010600707
19880927	-0.039694656
19880928	0.013513514
19881003	-0.027661358
19881025	-0.009836066
19881027	0.020695364
19881028	0.02189781
19881102	0.013957307
19881103	0.012145749
19881125	0.132511556
19881128	0.002040816
19881202	-0.008707301
19881227	0.05656697
19881228	-0.009230769
;run;

Thanks a lot for your time.

Best,

Cheema

[ChrisNZ · Accepted solution]

Like this?

data _F;
  retain FMTNAME 'ROLL12M' TYPE 'N' HLO 'M'; 
  do D=198701 to 201812;
    START=input(catt(D,'01'),?? yymmdd8.);
    if ^START then continue;
    END  =intnx('month',START,12)-1;
    LABEL=put(START,date9.)||'-'||put(END,date9.);
    output;
  end;
run;
proc format cntlin=_F ;
run;
proc means data=HAVE stddev;
  class DATE / mlf;
  format DATE roll12m.;
  var RET;
run;

Analysis Variable : RET
DATE	N Obs	Std Dev
01APR1987-31MAR1988	33	0.0151159
01APR1988-31MAR1989	31	0.0311378
01AUG1987-31JUL1988	40	0.0151391
01AUG1988-31JUL1989	17	0.0396385
01DEC1987-30NOV1988	41	0.0268591
01DEC1988-30NOV1989	3	0.0378381
01FEB1987-31JAN1988	26	0.0141094
01FEB1988-31JAN1989	38	0.0291518
01JAN1987-31DEC1987	23	0.0136973
01JAN1988-31DEC1988	41	0.0283068
01JUL1987-30JUN1988	41	0.0156088
01JUL1988-30JUN1989	20	0.0364963
01JUN1987-31MAY1988	40	0.0160055
01JUN1988-31MAY1989	24	0.0335636
01MAR1987-29FEB1988	29	0.0148302
01MAR1988-28FEB1989	35	0.0298514
01MAY1987-30APR1988	36	0.0154618
01MAY1988-30APR1989	28	0.0321538
01NOV1987-31OCT1988	41	0.0158081
01NOV1988-31OCT1989	7	0.0509791
01OCT1987-30SEP1988	40	0.0146485
01OCT1988-30SEP1989	11	0.0438623
01SEP1987-31AUG1988	40	0.0133277
01SEP1988-31AUG1989	14	0.0419713

[MAC1430]

Hi Chris,

Thanks.Just wondering if it is possible to show onlye one date for each cell. For example, to how only 31Aug1989 for last cell instead of 01Sep1988-31AUG1989.

Thanks for your help.

[ChrisNZ]

It is. Just modify the label.

[KachiM]

Cheema,

You have received a solution (SQL)  for almost same problem with a different subject line from KSHARP. You want an efficient solution now. Tell us your experiments with SQL solution and  any other method you tried. Give some results of run-times and memory issues.

regards,

DataSp

[MAC1430]

Hi,

Thanks. That solution was based on monthly returns, and now I am looking for based on daily returns in past 12 months.  I can use that code for daily one as well with few adjustments but the processing time increases.

Best,

Cheema