topic Re: Covariance of successive returns in SAS Programming

Covariance of successive returns

EJAA — Fri, 16 Jun 2017 05:11:44 GMT

Hello Everyone

Thanks in advance

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.

Re: Covariance of successive returns

Reeza — Fri, 16 Jun 2017 05:32:12 GMT

http://blogs.sas.com/content/iml/2010/12/08/computing-covariance-and-correlation-matrices.html

Re: Covariance of successive returns

Ksharp — Fri, 16 Jun 2017 13:04:45 GMT

or try

PROC CORR data=have COV;

by year stock;

var ret ret1;

run;

Re: Covariance of successive returns

EJAA — Fri, 23 Jun 2017 05:39:42 GMT

Hi Reeza

Thanks very much for the LINK. it was very helpful.

Regards

EJAA.

Re: Covariance of successive returns

EJAA — Fri, 23 Jun 2017 05:48:25 GMT

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

Thanks very much in advance.

Regards

EJAA

Re: Covariance of successive returns

Ksharp — Fri, 23 Jun 2017 12:46:11 GMT

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;

Re: Covariance of successive returns

EJAA — Sun, 25 Jun 2017 09:56:58 GMT

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.

Re: Covariance of successive returns

Ksharp — Sun, 25 Jun 2017 12:35:08 GMT

You could attach a TXT or CSV file .

OR a data step to show your data .

Re: Covariance of successive returns

EJAA — Mon, 26 Jun 2017 03:04:27 GMT

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.

Re: Covariance of successive returns

Ksharp — Mon, 26 Jun 2017 13:09:06 GMT

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.

Re: Covariance of successive returns

Ksharp — Mon, 26 Jun 2017 13:21:17 GMT

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;

Re: Covariance of successive returns

EJAA — Fri, 07 Jul 2017 03:36:50 GMT

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.

Re: Covariance of successive returns

Reeza — Fri, 07 Jul 2017 03:40:53 GMT

Post your code and log

Re: Covariance of successive returns

EJAA — Fri, 07 Jul 2017 05:00:24 GMT

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

Thanks very much Ksharp and Reeza for this great assistance.

ejaa

Re: Covariance of successive returns

Ksharp — Fri, 07 Jul 2017 14:22:14 GMT

OK. Here is .

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


data have1;
 set have;
 year=year(intnx('year.7',DATADATE,0,'b'));
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;

Re: Covariance of successive returns

EJAA — Sat, 08 Jul 2017 03:24:54 GMT

Thanks very much Ksharp. The covariance is solved. Really appreciate.

Please I have another question which i tried anwering. Now I want to find the average price (prc) for each fiscal year just like the covariance and tried this code but gives me error. Please what am I doing wrong in my codes.Any other suggestions for me. Thanks in advance Ksharp.

proc expand data=lid.sas out=lid.ave from=day to=year;
convert prc / observed=average;
id fyear;
by gvkey conm;
run;

Rgds

ejaa.

Re: Covariance of successive returns

Ksharp — Sat, 08 Jul 2017 04:55:06 GMT

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


data have1;
 set have;
 year=year(intnx('year.7',DATADATE,0,'b'));
run;
proc sort data=have1;
 by  gvkey year;
run;

proc summary data=have1;
 by gvkey year;
 var prc;
 output out=want mean=mean_prc ;
run;

Re: Covariance of successive returns

EJAA — Sat, 08 Jul 2017 06:14:41 GMT

Thanks once again Ksharp. It worked perfectly. Cant thank you enough for this great asisstance.

rgds

ejaa