Solved
Contributor
Posts: 40

# Covariance of successive returns

Hello Everyone

I have daily stock returns (Ret) and its lag one (Ret1) for the period of 2000-2010. I'm a new user and I want to calculate the covariance between Ret and Ret1 thus Cov(Ret,Ret1) for each individual stock for each year.

Please how can I solve the problem. Any assistance for me.

Thanks very much.

Accepted Solutions
Solution
‎07-07-2017 01:00 AM
Super User
Posts: 10,778

## Re: Covariance of successive returns

Here is an example to get the fiscal year. You can modify it to suit your data.

``````

data have;
do date='01jan2010'd to '01jan2014'd;
do stock=1 to 10;
ret=rand('normal');
ret1=rand('normal');
output;
end;
end;
format date date9.;
run;

data have;
set have;
year=year(intnx('year.7',date,0,'b'));
run;
proc sort data=have;
by  stock year;
run;

proc corr data=have noprint cov out=temp(where=(_type_='COV'));
by  stock year;
var ret ret1;
run;

data want;
set temp(keep=year stock ret1 rename=(ret1=cov_ret_ret1));
by  stock year;
if first.year;
run;

``````

All Replies
Super User
Posts: 23,724

Contributor
Posts: 40

Hi Reeza

Regards

EJAA.

Super User
Posts: 10,778

## Re: Covariance of successive returns

or try

PROC CORR data=have COV;

by year stock;

var ret ret1;

run;

Contributor
Posts: 40

## Re: Covariance of successive returns

Hello Ksharp

Thanks very much for the codes. It did work.

However the output is not in the format below.

Please How do I modify the codes so I can get the end output to be in the forward below.

Companyname           Date               Cov(ret ret1)

A                                 1990                       0.5

A                                 1991                       0.6

A                                 1992                       0.7

Regards

EJAA

Super User
Posts: 10,778

## Re: Covariance of successive returns

``````data have;
do year=2010 to 2016;
do stock=1 to 10;
do i=1 to 10;
ret=rand('normal');
ret1=rand('normal');
output;
end;
end;
end;
run;

proc corr data=have noprint cov out=temp(where=(_type_='COV'));
by year stock;
var ret ret1;
run;

data want;
set temp(keep=year stock ret1 rename=(ret1=cov_ret_ret1));
by year stock;
if first.stock;
run;``````
Contributor
Posts: 40

## Re: Covariance of successive returns

Please Ksharp thanks once again. I have tried all the codes given evera times but not working.dont know what is wrong. Please how can I attach a sample of my data for you to have a look at how my data looks like. Thanks in advance.

Regards EJAA.

Super User
Posts: 10,778

## Re: Covariance of successive returns

You could attach a TXT or CSV file .

OR a data step to show your data .

Contributor
Posts: 40

## Re: Covariance of successive returns

Please Ksharp attached is a sample of my data for assesment.I did little modification for the data dates.

So for each year, i calculated the fiscal year end for each firm in the sample. So please the covariance (rets retslag1) will start from the stfy and end using endfy for each year. Eg. for Cov (rets retslag1) 1990 for firm GFGC, should be from 1JUL1989-30JUN1990, then 1991 will be from 1JUL1990-30JUN1991. Thanks very much in advance.

Super User
Posts: 10,778

## Re: Covariance of successive returns

Sorry. I can't import that csv file. It seems it is corrupted.

you can use

group=intnx('year.7',date,0,'b');

to get that fiscal year. and after that use my code to get what you want.

Solution
‎07-07-2017 01:00 AM
Super User
Posts: 10,778

## Re: Covariance of successive returns

Here is an example to get the fiscal year. You can modify it to suit your data.

``````

data have;
do date='01jan2010'd to '01jan2014'd;
do stock=1 to 10;
ret=rand('normal');
ret1=rand('normal');
output;
end;
end;
format date date9.;
run;

data have;
set have;
year=year(intnx('year.7',date,0,'b'));
run;
proc sort data=have;
by  stock year;
run;

proc corr data=have noprint cov out=temp(where=(_type_='COV'));
by  stock year;
var ret ret1;
run;

data want;
set temp(keep=year stock ret1 rename=(ret1=cov_ret_ret1));
by  stock year;
if first.year;
run;

``````
Contributor
Posts: 40

## Re: Covariance of successive returns

Dear Ksharp

Please I apologise for the delay in updating you on your directions for the past weeks. Very unfortunate the csv file got corrupted. I followed your steps to obtain the start and fiscal year end but please getting the last section which is estimating the covariance (plag1 prc)  for each firm for each year using its fiscal year date is still proving difficult. I have followed your codes closely but still not getting it. Please I have attached a new csv file for assessment to ascertain what might be wrong. Thanks in advance Ksharp for the assistance. rgd ejaa.

Super User
Posts: 23,724

Contributor
Posts: 40

## Re: Covariance of successive returns

Reeza I rechecked my codes and corrected an error. This time it worked perfectly.

Thanks very much Ksharp and Reeza for this great assistance.

KR

ejaa

Super User
Posts: 10,778

## Re: Covariance of successive returns

OK. Here is .

``````proc import datafile='c:\temp\sass.csv' out=have dbms=csv replace;
run;

data have1;
set have;
run;
proc sort data=have1;
by  gvkey year;
run;

proc corr data=have1 noprint cov out=temp(where=(_type_='COV'));
by  gvkey year;
var PRC plag1;
run;

data want;
set temp(keep=year gvkey plag1 rename=(plag1=cov_ret_ret1));
by  gvkey year;
if first.year;
run;``````
☑ This topic is solved.