BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EJAA
Obsidian | Level 7

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

17 REPLIES 17
EJAA
Obsidian | Level 7

Hi Reeza

 

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

 

Regards

EJAA. 

Ksharp
Super User

or try 

PROC CORR data=have COV;

by year stock;

var ret ret1;

run;

EJAA
Obsidian | Level 7

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

Ksharp
Super User
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;
EJAA
Obsidian | Level 7

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. 

Ksharp
Super User

You could attach a TXT or CSV file .

OR a data step to show your data .

EJAA
Obsidian | Level 7

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.

Ksharp
Super User

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.

 

x.png

Ksharp
Super User

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;

EJAA
Obsidian | Level 7

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. 

 

Reeza
Super User

Post your code and log

EJAA
Obsidian | Level 7

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1249 views
  • 2 likes
  • 3 in conversation