Calculating rolling window covariance in SAS

[SasHelpNeeded]

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!

[Ksharp]

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

[SasHelpNeeded]

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

[RW9]

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.

[udo_sas]

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;

[SasHelpNeeded]

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.

[udo_sas]

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... including Moving Time Window Operators.

Thanks,

Udo

[udo_sas]

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.h... - (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.

[SasHelpNeeded]

Thanks! That was helpful!