Adding book value per share (CCM) to monthly CRSP data set
Posted: 14 January 2015 08:43 AM   [ Ignore ]
Newbie
Rank
Total Posts:  5
Joined  2015-01-14

I have a monthly returns datafile from CRSP with PERMNO identifying each firm. For each month I would like to add the most current book value per share, which I have in an annual datafile from the CRSP Compustat Merged Database. In my CCM datafile I have the datadate field, but I am not sure how to correctly combine this with the calendar dates in the monthly datafile. I am aware that several others have mentioned similar problems, but even after reading their answers, I am still not sure how to do it.

Specifically, if I have a firm called AAA (in the annual CCM datafile) with datadate = ‘31-May-2010’, ‘31-May-2011’,... and book values per share = 30, 40,... And I have monthly returns for AAA (in the CRSP datafile) for date = ‘31-Mar-2011’, ‘30-Apr-2011’, ‘31-May-2011’, ‘30-Jun-2011’. Is the correct way of adding the most current book value per share (BKVLPS) to the monthly datafile, to do date = ‘31-Mar-2011’, ‘30-Apr-2011’, ‘31-May-2011’, ‘30-Jun-2011’ to set BKVLPS = 30,30,40,40???

Thanks!

Profile
 
 
Posted: 15 January 2015 02:58 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Maagen,

Something along these lines:

data returns;
  
input @01 id        
    
@03 date  MMDDYY10.
        @
14 return   
 ;
 
format date date9.;
datalines;
1 10/31/2013 0.01
1 11
/30/2013 0.02
1 12
/31/2013 0.03
1 01
/31/2014 -0.01
1 02
/28/2014 0.01
2 10
/31/2013 -0.01
2 11
/30/2013 0.02
2 12
/31/2013 0.01
2 01
/31/2014 -0.02
2 02
/28/2014 -0.03
2 03
/31/2014 0.02 
run
;

data yearly;
  
input @01 id        
    
@03 date  MMDDYY10.
        @
14 equity   
 
;
 
format date date9.;
datalines;
1 12/31/2011 8
1 12
/31/2012 10
1 12
/31/2013 11
2 12
/31/2012 30
2 12
/31/2013 28
run
;

proc sql;
 
create table result as 
  
select a.*, b.equityb.date as yearly_date 
  from returns a
yearly b 
  where  a
.id b.id 
  
and  a.date >= b.date 
  group by a
.ida.date 
  having max
(b.date) = b.date;
quit

best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 28 January 2015 09:51 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  5
Joined  2015-01-14

Hi Joost,

I get an error in the sql select: “Column ‘b.date’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.” Have you tested it on the data? thanks

Profile
 
 
Posted: 28 January 2015 03:35 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

The code I posted creates 2 datasets and does the join you need. I don’t have issues running it:

8602   proc sql;
8603    create table result as
8604     select a.*, b.equityb.date as yearly_date
8605     from returns a
yearly b
8606     where  a
.id b.id
8607     
and  a.date >= b.date
8608     group by a
.ida.date
8609     having max
(b.date) = b.date;
NOTEThe query requires remerging summary statistics back with the original data.
NOTETable WORK.RESULT createdwith 11 rows and 5 columns.

8610   quit;
NOTEPROCEDURE SQL used (Total process time):
      
real time           0.22 seconds
      cpu time            0.03 seconds 

If you apply the same mechanism using other datasets, you probably need to change variable names (e.g. use ‘datadate’ instead of ‘date’).

best regards,

Joost

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 29 May 2015 02:07 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  4
Joined  2015-04-22

Hi,
I was wondering whether it is wrong to match data from the CCM with CRSP (monthly) data using as identifier Lpermno. Basically,  considering Lpermno the same as permno, and disregarding the info on gvkey. Or something more sophisticated is the right way to go?

Profile
 
 
Posted: 29 May 2015 03:59 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Greenmoose,

It is probably best to do both and compare differences. Although permno is not supposed to change, it still sometimes does.

Best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile