Help using Base SAS procedures

Calculating rolling window covariance in SAS

Reply
Occasional Contributor
Posts: 5

Calculating rolling window covariance in SAS

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!

Super User
Posts: 9,691

Re: Calculating rolling window covariance in SAS

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

Occasional Contributor
Posts: 5

Re: Calculating rolling window covariance in SAS

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

Super User
Super User
Posts: 7,432

Re: Calculating rolling window covariance in SAS

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.

SAS Employee
Posts: 416

Re: Calculating rolling window covariance in 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;

Capture.JPG

Occasional Contributor
Posts: 5

Re: Calculating rolling window covariance in SAS

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.

SAS Employee
Posts: 416

Re: Calculating rolling window covariance in 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

SAS Employee
Posts: 416

Re: Calculating rolling window covariance in 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.

Occasional Contributor
Posts: 5

Re: Calculating rolling window covariance in SAS

Thanks! Smiley Happy That was helpful!

Ask a Question
Discussion stats
  • 8 replies
  • 941 views
  • 7 likes
  • 4 in conversation