BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MAC1430
Pyrite | Level 9

Dear all,

 

I want to calculate rolling volatility based on past 12 month returns i.e., from July 1997 to June 1998.  Furthermore, if a month is missing in past 12 months then volatility calculation should be based on 11 months  between the period of July 1997 to June 1998 instead of using one earlier month i.e, June 1997. 

 

Thanks a lot for your time.

 

Best,

 

MAC

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
You'd better post your data at this forum, not attachment. No one would like to download it .


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;


View solution in original post

15 REPLIES 15
Patrick
Opal | Level 21

Please provide sample data as as SAS Data step creating such data. Then explain us how the expected result should look like (also as an example using data).

MAC1430
Pyrite | Level 9

Thanks a lot for your prompt response. Sorry, I did not explain it earlier. 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.

Reeza
Super User

Do you have a SAS ETS licence? If so, look at proc Expand. The documentation, example on transformations, is what you're looking for. 

MAC1430
Pyrite | Level 9

Dear Reeza,

 

Thanks. YEs, I have SAS license and I used it to calculate volatility. But the problem is that it calculates based on past 12 observations instead of just past 12 months. For example, if one month is missing in past 12 months period i.e., July is missing from January to December 1985 then it will use monthly return from December 1984 too. I am using teh following code in SAS:

Data ret;
infile 'D:\My SAS Files\9.4\US\US paper data\ret.dat';
Input stock $1-5 date1 $7-14 exc $16-17 sharecode $19-20 ret;
run;
data ret1;
set ret;
if sharecode^=10 and sharecode^=11 then delete;
date=SUBSTRN(date1,1,6);
year= SUBSTRN(date1,1,4);
month= SUBSTRN(date1,5,2);
run;
data have(keep=stock ret monthDate);
set ret1;
monthDate = mdy(month, 1, year);
if ret=-66 then delete;
if ret=-77 then delete;
if ret=-88 then delete;
if ret=-99 then delete;
if ret=. then delete;
run;
proc sql;
create table roll as
select
h2.stock,
h2.monthDate as periodEndDate format=yymmd7.,
h2.ret,
mean(h1.ret) as meanRet,
std(h1.ret) as stdRet
from
have as h1 inner join
have as h2 on h1.stock=h2.stock and
intck("MONTH", h1.monthDate, h2.monthDate) between 0 and 11
group by h2.stock, h2.monthDate, h2.ret
having count(h2.stock)=12;
quit;

Reeza
Super User

SAS ETS licence. SAS licences different modules that do different things. ETS is Econometrics and Time Series which handles serial data such as time and date, and is capable of filling in missing periods or accounting for it. 

 

Your query looks close, except for your having clause which requires that you have 12 months of data. This is contradictory to what is specified in your requirements, I think it's 9 now? 

 

MAC1430
Pyrite | Level 9

Thanks a lot for your help, another member posted the solution I was looking for.

MAC1430
Pyrite | Level 9

Dear Reeza,

 

Thansk for your prompt response. I am defining volatility based on standard deviation form past 12 month returns. I have attached the file as well.

Ksharp
Super User
You'd better post your data at this forum, not attachment. No one would like to download it .


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;


MAC1430
Pyrite | Level 9

Thanks a lot, its really helpful. I will post data in future if I have a question.

MAC1430
Pyrite | Level 9

I just found a small problem with it, the code works very well for the samll data-set but it stops working for large data-set. Any idea how to fix the code to work for a large data-set? Thanks

Reeza
Super User

What does stop working mean? Why does it stop working?

Whats a large data set? 

MAC1430
Pyrite | Level 9
Its like 4 million observations. I am running the code and its taking ages to provide results.
Ksharp
Super User
OK. If you have a big table, Try Hash Table. 
If the following code still couldn't work. I can use DOW+Hash Table .



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;
data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have',hashexp:20);
  h.definekey('stock','date');
  h.definedata('ret');
  h.definedone();
 end;
set have;
array x{12} _temporary_;
n=0;
call missing(of x{*});

do i=-11 to 0;
 temp=intnx('month',date,i);
 rc=h.find(key:stock,key:temp);
 if rc=0 then do;n+1;x{n}=ret;end;
end;

if n ge 9 then volatility=std(of x{*});
 else volatility=.;
drop i n temp rc;
run;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 15 replies
  • 6182 views
  • 6 likes
  • 4 in conversation