topic Re: Calculating rolling window covariance in SAS in SAS Procedures

Calculating rolling window covariance in SAS

SasHelpNeeded — Tue, 06 May 2014 12:02:50 GMT

Hi! I am totally new to SAS and primarily use R. I have been struggling with this problem since last two days. My data is as below -

Comp date time returns 1 12-Aug-97 10:23:38 0.919292648 1 12-Aug-97 10:59:43 0.204139521 1 13-Aug-97 11:03:12 0.31909242 1 14-Aug-97 11:10:02 0.989339371 1 14-Aug-97 11:19:27 0.08394389 1 15-Aug-97 11:56:17 0.481199854 1 16-Aug-97 13:53:45 0.140404929 1 17-Aug-97 10:09:03 0.538569786 2 14-Aug-97 11:43:49 0.427344962 2 14-Aug-97 11:48:32 0.154836294 2 15-Aug-97 14:03:47 0.445415114 2 15-Aug-97 9:38:59 0.696953041 2 15-Aug-97 13:59:23 0.577391987 2 15-Aug-97 9:10:12 0.750949097 2 15-Aug-97 10:22:38 0.077787596 2 15-Aug-97 11:07:57 0.515822161 2 16-Aug-97 11:37:26 0.862673945 2 17-Aug-97 11:42:33 0.400670247 2 19-Aug-97 11:59:34 0.109279307

This is just a sample data. It has company, date and time level returns on share price for a period of 10 years. I need to calculate autocovariance (degree 1) of returns over a period of 10 days for each Comp and date value combination. As you can see, my time series is not continuous, it has breaks for weekends and public holidays. In such cases, if i need to take a 10 day range, I can't use a intnk function as adding 10 days to the date column might include a saturday/sunday for which I don't have data for and hence, my autocovariance value will be compromised. How do I make this range dynamic?

I have gotten till this part -

data ds1; 
set ds; 
by comp; 
LAG_RET = lag(RET); 
IF FIRST.comp THEN DO; 
LAG_RET = .; 
END; 
run;

For calculating correlation and covariances -

proc corr data=ds1 outp=Corr 
by comp date; 
cov; /** include covariances **/ 
var ret lag_ret; 
run;

But how do I insert the event window for each date, store_id combination? My final output should look something like this -

Comp Date autocovariance

1 12-Aug-97 ...

1 13-Aug-97 ...

If anyone can suggest a methodology to do this, It would be really helpful.

Thanks!

Re: Calculating rolling window covariance in SAS

Ksharp — Tue, 06 May 2014 12:35:05 GMT

For Cov(X,Y)

what is X and what is Y ?

e.g.

X is

Comp date time returns

1 12-Aug-97 10:23:38 0.919292648

1 12-Aug-97 10:59:43 0.204139521

and Y is what ?

and there might be only one obs for X or for Y ,is it right ?

Xia Keshan

Re: Calculating rolling window covariance in SAS

RW9 — Tue, 06 May 2014 13:08:20 GMT

Hi,

There are some other posts in the Communities where people are using intnx function and some formulae to create time windows. Maybe not exactly the same as you but could be adapted. Here is one I just got from a search:

https://communities.sas.com/message/204913#204913

Do a search on intnx weekends, there are 6 results.

Re: Calculating rolling window covariance in SAS

SasHelpNeeded — Wed, 07 May 2014 05:51:57 GMT

Since I am calculating autocovariance, my x would be returns and y would be lag_returns.

Re: Calculating rolling window covariance in SAS

udo_sas — Wed, 07 May 2014 18:10:49 GMT

Hello -

If you have access to SAS/ETS software you may want to explore the TIMESERIES procedure.

Thanks,

Udo

Example:

data have;

input Comp date date9. time time8. returns;

format date date9. time time9.;

datalines;

1 12-Aug-97 10:23:38 0.919292648

1 12-Aug-97 10:59:43 0.204139521

1 13-Aug-97 11:03:12 0.31909242

1 14-Aug-97 11:10:02 0.989339371

1 14-Aug-97 11:19:27 0.08394389

1 15-Aug-97 11:56:17 0.481199854

1 16-Aug-97 13:53:45 0.140404929

1 17-Aug-97 10:09:03 0.538569786

2 14-Aug-97 11:43:49 0.427344962

2 14-Aug-97 11:48:32 0.154836294

2 15-Aug-97 14:03:47 0.445415114

2 15-Aug-97 9:38:59 0.696953041

2 15-Aug-97 13:59:23 0.577391987

2 15-Aug-97 9:10:12 0.750949097

2 15-Aug-97 10:22:38 0.077787596

2 15-Aug-97 11:07:57 0.515822161

2 16-Aug-97 11:37:26 0.862673945

2 17-Aug-97 11:42:33 0.400670247

2 19-Aug-97 11:59:34 0.109279307

run;

proc timeseries data=have out=_null_ outcorr=outcorr;

id date interval=day accumulate=total;

var returns;

by comp;

run;

Re: Calculating rolling window covariance in SAS

SasHelpNeeded — Thu, 08 May 2014 05:57:21 GMT

Wow! This seems like a great way of working with Time Series. But is there a way I can set up a rolling window to calculate all the values using this? I need the data at a Comp, Date level.

Re: Calculating rolling window covariance in SAS

udo_sas — Thu, 08 May 2014 13:44:14 GMT

Hello -

You may have to consider writing a macro which loops through the rolling window you have in mind - TIMESERIES will use all data at hand.

As an alternative you may want to check out the EXPAND procedure, which allows you to transform time series data at hand and which provides you access to many out-of-the-box transformations: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_expand_sect026.htm including Moving Time Window Operators.

Thanks,

Udo

Re: Calculating rolling window covariance in SAS

udo_sas — Thu, 08 May 2014 14:16:52 GMT

PS: I should have mentioned that the new TIMEDATA procedure provides you with capabilities you might be interested in as well: http://support.sas.com/documentation/cdl/en/etsug/66840/HTML/default/viewer.htm#etsug_timedata_toc.htm - (aka Ken) has recorded a nice getting started video: Working with Time Series Data Using SAS/ETS - YouTube which you may want to check out.

Re: Calculating rolling window covariance in SAS

SasHelpNeeded — Wed, 14 May 2014 08:59:13 GMT

Thanks! That was helpful!