Merging Director Database and ExecuComp Database
Posted: 04 January 2013 11:27 AM   [ Ignore ]
Newbie
Rank
Total Posts:  5
Joined  2013-01-04

Hi All,

Does anyone know how to merge the Director data (from the Director database, as subset of RiskMetrics) to the ExecuComp? What are the identifiers to merge between these two databases.

I want to have information about the whether the executives hold vice presidents position, the number of vice presidents of the top five executives => so need to combine data for each individual executive. Using either the director’s full name, first name or last name is troublesome, since each database have different forms. For example, one database states the name as Robert, but the other states Rob. And many other forms, need to justify a lot.

I would appreciate if anyone know how to merge these two databases!

Thanks,
Cheers,

Helen.

Profile
 
 
Posted: 04 January 2013 11:32 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Helen,

Both Execucomp and the RiskMetrics director datasets have cusip. That should work fine.

Other than that, Execucomp has ‘gvkey’ and Riskmetrics have their own id. That doesn’t seem to be helpful for matching. In general, matching on ticker symbol/firmname is messy (ticker symbols get recycled, firmnames can change as well).

Hope this helps,

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: 04 January 2013 11:42 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  5
Joined  2013-01-04

Hi Joost,

Cusip works fine if only merge one director to one director per firm. But if I want to merge each of the directors in Director to each in ExecuComp.

Is there any additional identifier need?

Thanks,
Helen.

Profile
 
 
Posted: 04 January 2013 11:51 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Helen,

Ok; I understand. That is tricky smile

One way that might be worth a show it to match on the director’s name.

Spedis is a SAS function that scores the ‘similarity’ of strings. You could apply this function to the full name variables (FULLNAME in Directors and EXEC_FULLNAME in Execucomp).

See: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245949.htm

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: 04 January 2013 11:57 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  5
Joined  2013-01-04

I will try!

Thanks Joost!

Profile
 
 
Posted: 04 January 2013 12:17 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

ok, good luck!

You will need to play a little to find the right ‘cutoff’ for spedis. (too strict => losing good observations, too loose => including bad observations).

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: 11 January 2013 05:04 PM   [ Ignore ]   [ # 6 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Hi Helen,

I was working on a similar project. I attached my code with this post.

Bascially, I did in the same way as suggested by Joost.

Let me know if you think there is a bug/bugs in the code.

After we export the possible matches, then we will hand select the good match ones from excel.

It is about 5000 obs left over, it is doable.

Credits go to Joost.

Cheers,
Zenghui

Joost Impink - 04 January 2013 11:51 AM

hi Helen,

Ok; I understand. That is tricky smile

One way that might be worth a show it to match on the director’s name.

Spedis is a SAS function that scores the ‘similarity’ of strings. You could apply this function to the full name variables (FULLNAME in Directors and EXEC_FULLNAME in Execucomp).

See: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245949.htm

best regards,

Joost

File Attachments
director_match.sas  (File Size: 2KB - Downloads: 516)
 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 11 January 2013 09:56 PM   [ Ignore ]   [ # 7 ]
Newbie
Rank
Total Posts:  5
Joined  2013-01-04

Hi Zenghui,

Actually, I figure out how to collect my data without merging between two databases. However, have to say that the Spendis function, as suggested by Joost is quite good.

I have one question in regarding to your code: why are you using the “Select Distinct” function? Isn’t that to reduce the duplicate values? The obs falls from over 200,000 to only 42,000 (from one database). Why do you want to reduce the duplicate values (since the values for these variable execid, EXEC_FNAME, EXEC_LNAME, EXEC_MNAME,cusip will be same for all year, but values for other will be different)? As a result, using the Select Distinct will reduce the obs significantly!:

PROC SQL;
CREATE TABLE CEO_SUMMARY AS
SELECT DISTINCT execid, EXEC_FNAME, EXEC_LNAME, EXEC_MNAME,cusip
        from CEO
quit; 

You didn’t include the year variable as well. I try to include the “year"variable in the above code and the number of obs remain the same.

One explanation I can think of is that you do it for one unique value, check in excels, then merge the two databases with this good_merged.

Sorry for asking lots of question (since I’m newbie) smile

Cheers,
Helen.

Profile
 
 
Posted: 11 January 2013 10:57 PM   [ Ignore ]   [ # 8 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

HI Helen,

I am newbie too.  smile

the reason i select “distinct” and not including “year” is that i want to generate a matching table between these two database files first (with unique execid from execcomp file, and unique id from the directors file).

becasue of less obs, we will spend less time on the excel file. Once the excel file is ready, we can use this excel table to merge these two data file by computer.

do you think these two step procedures will work?

Have a nice day,
Zenghui

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 14 January 2013 05:16 AM   [ Ignore ]   [ # 9 ]
Newbie
Rank
Total Posts:  5
Joined  2013-01-04

Hi Zenghui,

Yes, it will work though I have not tried it. It’s a good idea! I may apply that idea to my project.

Btw, when merging the Compustat, Execucomp and RiskMetrics databases, do u use CUSIP as identifier when merging?

I got difficulties in changing the forms of the CUSIP!

Thanks!
Cheers,
Helen.

Profile
 
 
Posted: 22 February 2013 03:08 PM   [ Ignore ]   [ # 10 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Helen,

I think my code is not complete.

Please refer to this paper for more informaiton:
http://www.sfs.org/Paper for Cavalcade website/Co-opted Boards.pdf


Thanks,
Zenghui

 Signature 

Zenghui
A humble student of business

Profile