Dynamic Rolling Window in SAS for correlation calculation

Question

Problem: I have a data set 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

These are nothing but share price returns for every company at a date and time level. I need to calculate autocorrelation(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 autocorrelation value will be compromised. How do I make this range dynamic?

I found this question Calculating rolling correlations in SAS that I thought might help but then again, there is the same intnx problem.

Joe Joe · Accepted Answer · 2014-05-05T15:31:27

You can use the INTERVALDS system option to define a custom interval that fits your needs. See this article for more details.

The basic concept is that you create a dataset containing all of your possible dates (or datetimes) and define an interval value for each one, then tell SAS via the system option to use that dataset when you use a particular interval name. Then use INTNX as normal.

Otherwise, you could just do a PROC FREQ of your data to get the unique days, and then use that to create a day counter; then instead of creating your fromDate with intnx, you can just use SQL to grab the row with a date 10 less than current date.

Dynamic Rolling Window in SAS for correlation calculation

1 Answers