BookmarkSubscribeRSS Feed
SasHelpNeeded
Calcite | Level 5

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!

8 REPLIES 8
Ksharp
Super User

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
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Employee

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

SasHelpNeeded
Calcite | Level 5

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
SAS Employee

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
SAS Employee

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
Calcite | Level 5

Thanks! Smiley Happy That was helpful!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 3518 views
  • 7 likes
  • 4 in conversation