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.
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;
Hi Reeza
Thanks very much for the LINK. it was very helpful.
Regards
EJAA.
or try
PROC CORR data=have COV;
by year stock;
var ret ret1;
run;
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
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;
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.
You could attach a TXT or CSV file .
OR a data step to show your data .
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.
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.
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;
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.
Post your code and log
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
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;
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.