topic Re: Cross-correlation across all stocks in SAS Programming

Cross-correlation across all stocks

somebody — Tue, 04 Aug 2020 02:04:35 GMT

I have a dataset of daily stock returns and trading volume. I want to check the cross-correlogram between returns and trading volumes across stocks. How do I achieve this? Right now, my code can get the CCF for each stock. Do I just get the average across stocks using PROC MEANS? or is there a way to do it without the averaging step?

proc timeseries data=sample outcrosscorr=crosscor;
	var volume r;
	by stock;
	id date interval=weekday; 
	crosscorr lag n ccf;
run;

Re: Cross-correlation across all stocks

mkeintz — Tue, 04 Aug 2020 04:56:02 GMT

The proc timeseries you present will do crosscorrelations of r and vol WITHIN stocks, not across stocks.

To get cross correlations you will need to reshape the data, so that there are two columns (i.e. two variables) for each stock. And of course, you'll quickly run into scalability problems as the number of stocks increases - for N stocks, it will produce N*(N-1) cross-corellation series (defaults to lag15 to lead15 I believe).

Re: Cross-correlation across all stocks

somebody — Tue, 04 Aug 2020 06:49:41 GMT

yeah I mean just within stocks, I don't expect Volume of stock A correlates with returns of stock B. So I can just compute the cross-correlation within each stock, and then average across them?

Re: Cross-correlation across all stocks

Ksharp — Tue, 04 Aug 2020 11:51:10 GMT

Better post it at Forecasting forum .

Re: Cross-correlation across all stocks

mkeintz — Tue, 04 Aug 2020 23:51:44 GMT

Do you want simple averages or weighted averages?

Re: Cross-correlation across all stocks

somebody — Wed, 05 Aug 2020 00:28:49 GMT

I did simple

Re: Cross-correlation across all stocks

mkeintz — Wed, 05 Aug 2020 00:51:24 GMT

Your original questions were:

Do I just get the average across stocks using PROC MEANS?

Ans: Yes

or is there a way to do it without the averaging step?

Not that I know of.

Since the timeseries output dataset (crosscor in your example) identifies correlations by

_name_ (the first var)
_cross_ (the other var)
lag (offset, which appears to have a default range of 15 to -15

you should use them as the class identifiers, with ccf as the analysis variable.

proc means data=crosscor noprint  nway;
  var ccf;
  class _name_ _cross_ lag;
  output out=crosscor_means mean=;
run;

crosscor_means has what you want. But note it has some duplication. It presents correlations of X and lag(Y) for lag=-15 through lag=15 (one of them is lead15, but I didn't bother to check). That's 31 rows, but the dataset actually has 62 rows, because it also presents correlations of Y and lag(X), again for lag=-15 through 15. So CCF for x with y for the lag=-10 row is the same value as CCF for y with x for the lag=+10 row.