BookmarkSubscribeRSS Feed
Takdir
Obsidian | Level 7

HI,

I want to calculate volatility using stanadard deviation over past months only if there are atleast 9 month observations. I have attached an excel sheet for illustration. For example in Dec 1985, I calculate volatility based on monthly returns from January-December 1985 and it has data for all 12 months in 1985. However, I calculate volatility based on monthly returns from August 1985 to July 1985 but it has data for 10 months in a 12 month period. I do not calculate volatility for May1987 because it does not have 9 month observations in past 12 months i.e from June 1986 to May 1987.

 

the above mentioned problem was given by another user. I made modification to it by adding another stock. so can anyone help me with how can I calculate volatility for multiple stocks?

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret stdret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505 0.029861527
1 198601 -0.04 0.030770009
1 198602 -0.1023 0.030523464
1 198603 -0.0163 0.035403028
1 198604 -0.14465 0.040839661
1 198607 -0.07383 0.043861268
1 198608 -0.0315 0.046163063
1 198609 0.05495 0.060118401
1 198610 -0.15368 0.061979498
1 198611 -0.11494 0.063860041
1 198612 -0.03578 0.06457195
1 198705 -0.1234
2 198501 -0.10435
2 198502 -0.04762
2 198503 -0.08154
2 198504 -0.06757
2 198505 -0.08911
2 198506 -0.05481
2 198507 -0.0628
2 198508 -0.0989
2 198509 -0.10811
2 198510 -0.14205
2 198511 -0.04286
2 198512 -0.05505 0.029861527
2 198601 -0.04 0.030770009
2 198602 -0.1023 0.030523464
2 198603 -0.0163 0.035403028
2 198604 -0.14465 0.040839661
2 198607 -0.07383 0.043861268
2 198608 -0.0315 0.046163063
2 198609 0.05495 0.060118401
2 198610 -0.15368 0.061979498
2 198611 -0.11494 0.063860041
2 198612 -0.03578 0.06457195
2 198705 -0.1234
;
run;

 

 

the original problem and solution from the previous post by that user is given below:

I think this solution has a problem as well. it calculates volatility for 9 months as well even if there is no missing months.

 

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret stdret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505 0.029861527
1 198601 -0.04 0.030770009
1 198602 -0.1023 0.030523464
1 198603 -0.0163 0.035403028
1 198604 -0.14465 0.040839661
1 198607 -0.07383 0.043861268
1 198608 -0.0315 0.046163063
1 198609 0.05495 0.060118401
1 198610 -0.15368 0.061979498
1 198611 -0.11494 0.063860041
1 198612 -0.03578 0.06457195
1 198705 -0.1234
;
run;
proc sql;
create table want as
select *,case when (
(select count(ret) from have where stock=a.stock and
date between intnx('month',a.date,-11) and a.date) ge 9 )
then (select std(ret) from have where stock=a.stock and
date between intnx('month',a.date,-11) and a.date)
else . end as volatility
from have as a;
quit;

1 REPLY 1
unison
Lapis Lazuli | Level 10

I'm trying to piece together what exactly you want. Please post an example of the "want" dataset in the future. From what I understand, you want to calculate a rolling 12 month volatility for a number of stocks (but only if 9 of the 12 months are nonmissing). Adapting @s_lassen's response on this thread (https://communities.sas.com/t5/SAS-Programming/calculating-rolling-standard-deviation-of-stock-retur...😞

 

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505
1 198601 -0.04
1 198602 -0.1023
1 198603 -0.0163
1 198604 -0.14465
1 198607 -0.07383
1 198608 -0.0315
1 198609 0.05495
1 198610 -0.15368
1 198611 -0.11494
1 198612 -0.03578
1 198705 -0.1234
2 198501 -0.10435
2 198502 -0.04762
2 198503 -0.08154
2 198504 -0.06757
2 198505 -0.08911
2 198506 -0.05481
2 198507 -0.0628
2 198508 -0.0989
2 198509 -0.10811
2 198510 -0.14205
2 198511 -0.04286
2 198512 -0.05505
2 198601 -0.04
2 198602 -0.1023
2 198603 -0.0163
2 198604 -0.14465
2 198607 -0.07383
2 198608 -0.0315
2 198609 0.05495
2 198610 -0.15368
2 198611 -0.11494
2 198612 -0.03578
2 198705 -0.1234
;
run;

proc sort data=have;
by stock date;
run;

proc timeseries data=have out=have_ts;
	by stock;
	id date interval=month;
	var ret;
run;

data want;
	set have_ts;
	by stock;
	array returns(0:11) 8 _temporary_;
	if first.stock then call missing(of returns(*));
	returns(mod(_n_,12))=ret;
	if n(of returns(*))>9 then stdret=std(of returns(*));
run;

 It's best to try to fill in missing months with observations so that the calculation works properly (this is why I first create a timeseries with the have set).

 

-unison

-unison

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1463 views
  • 0 likes
  • 2 in conversation