data WANT;
input YEAR $ return $ market return;
datalines;
1990 0.11 0.05
1991 0.02 0.02
1992 0.03 0.05
1993 0.04 0.03
1994 0.05 0.08
1995 0.06 0.09
1996 0.07 .
1997 0.08 0.09
1998 0.07 0.07
1999 0.14 0.20
2000 0.10 0.11
The above is a simple example of my dataset.
I am trying to calculate moving covariance(between return and market return) and moving variance of market returns.
As you may guess, I at the end want to have beta but the beta I need to have is not a coefficient from regression. The paper I follow did this way so I need to calculate cov(Rm, Ri) and Var(Rm).
I first thought to utilize RPOC EXPAND and yes, this at least gives me the result of variances by obtaining moving standard deviations from MOVSTD and I square them. Nonetheless, there is no command calculating COVARIANCE, quite sadly.
Do anyone of you know how to calculate covariances given with the data like the above? Also, how can I deal with missing values when I use your own codes?
Moving covariance is tricky and what I have found so far is moving covariance for time-series only.
I appreciate your helps a lot!!!
Hi Paige,
I did found the paper you suggested once but did not think to revise the codes for my case.
I did revise some and share this with other users who may face similar difficulties.
proc expand DATA = result OUT = result2;
id date;
convert VWRETD = YISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert ret = XISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert prodxiyi = PRODXIYISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert obs = N / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert ret = XICSS / METHOD = none TRANSFORMOUT = (movcss 60 trimleft 36);
convert VWRETD = YICSS / METHOD = none TRANSFORMOUT = (movcss 60 trimleft 36);
convert VWRETD = STDrm / METHOD = none TRANSFORMOUT = (movstd 60 trimleft 36);
convert ret = STDret / METHOD = none TRANSFORMOUT = (movstd 60 trimleft 36);
by cusip;
run;
data result3; set result2;
R = (prodxiyisum - (yisum*xisum)/n) / ( sqrt(xicss)*sqrt(yicss)) ;
cov=R*STDrm*STDret;
var=(STDrm)**2;
beta=cov/var;
yyyy=year(date);
if n="." then delete;
if 1990<=yyyy<=2019;
run;
What do you mean by 'deal with' missing values?
Also, do you have multiple groups/IDs in your data, and should they be handled as well?
For the data you provided I think that something like below may be an option:
data HAVE;
input YEAR $ return market_return;
datalines;
1990 0.11 0.05
1991 0.02 0.02
1992 0.03 0.05
1993 0.04 0.03
1994 0.05 0.08
1995 0.06 0.09
1996 0.07 .
1997 0.08 0.09
1998 0.07 0.07
1999 0.14 0.20
2000 0.10 0.11
;
run;
%let window = 3; /* one less than what you need */
data want;
if &window.<2 then stop; /* prevent to short window */
set have curobs=curobs;
/* in case you need it in groups uncomment below 2 lines */
/*
by SOME_GROUPING_VARIABLE;
if first.SOME_GROUPING_VARIABLE then call missing(of MR[*], of R[*]);
*/
array R[0:&window.] _temporary_;
array MR[0:&window.] _temporary_;
R[mod(curobs,&window)] = return;
MR[mod(curobs,&window)] = market_return;
/* variance */
if nmiss(of MR[*]) < &window. then MRvariance = var(of MR[*]);
/* covariance */
if nmiss(of MR[*]) < &window. and nmiss(of R[*]) < &window. then
do;
MRavg = mean(of MR[*]);
Ravg = mean(of R[*]);
s = 0; n = 0;
do i = 0 to &window.;
if not nmiss(MR[i],R[i]) then
do;
s + (MR[i]-MRavg)*(R[i]-Ravg);
n + 1;
end;
if n<2 then MR_R_covariance =.;
else MR_R_covariance = s/(n-1);
end;
end;
run;
proc print data = want;
run;
all the best
Bart
In your earlier thread about moving averages, I pointed to https://support.sas.com/kb/25/027.html which allows you to compute moving averages in a data set. The same logic allows you to compute moving covariances (except instead of the part where you compute a moving average, you have to program it to compute the moving covariance).
You can look up the formula for covariance, and then replace the calculation of the mean in the DATA step example with the calculation of a covariance.
There's also Example 2 in this paper which produces a moving correlation coefficient with PROC EXPAND; if you can calculate moving correlation then you can use the same ideas to calculate a moving covariance.
Hi Paige,
I did found the paper you suggested once but did not think to revise the codes for my case.
I did revise some and share this with other users who may face similar difficulties.
proc expand DATA = result OUT = result2;
id date;
convert VWRETD = YISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert ret = XISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert prodxiyi = PRODXIYISUM / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert obs = N / METHOD = none TRANSFORMOUT = (movsum 60 trimleft 36);
convert ret = XICSS / METHOD = none TRANSFORMOUT = (movcss 60 trimleft 36);
convert VWRETD = YICSS / METHOD = none TRANSFORMOUT = (movcss 60 trimleft 36);
convert VWRETD = STDrm / METHOD = none TRANSFORMOUT = (movstd 60 trimleft 36);
convert ret = STDret / METHOD = none TRANSFORMOUT = (movstd 60 trimleft 36);
by cusip;
run;
data result3; set result2;
R = (prodxiyisum - (yisum*xisum)/n) / ( sqrt(xicss)*sqrt(yicss)) ;
cov=R*STDrm*STDret;
var=(STDrm)**2;
beta=cov/var;
yyyy=year(date);
if n="." then delete;
if 1990<=yyyy<=2019;
run;
It's true theatproc expand can only generate moving statistics on univariate series, which excludes moving covariances.
BUT ... you can
In other words, for a windows of size 6 you would have
one sixth of the sum of product,
minus
one thirty-sixth of the sum of return times the sum of market_return.
As to missing values, if they are rare, I'd just have 5 data points in my 6-year windows (i.e. in your data, all the windows that normally would include 1996). Just be sure to drop ALL the variables for 1996, not just market_return.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.