How to match Execucomp (Outstanding Equity Awards) with IBES (both Guidance - and Academic Detail History)
Posted: 22 December 2015 07:47 AM   [ Ignore ]
Newbie
Rank
Total Posts:  4
Joined  2015-12-22

Hi,

I’m trying to merge data from Execucomp (Outstanding Equity Awards) with data from IBES on both Guidance - and Academic Detail History.
My research focuses on the S&P 1500 during 2007 - 2014. The Execucomp database forms the perfect reference point as it contains all companies that are or were active in the S&P 1500.

From the Execucomp database I’m able to retrieve the following identifiers; CO_PER_ROL, GVKEY, CUSIP, TICKER, CONAME and (YEAR).
In order to collect data from IBES > Academic > Detail History, I need to enter either the Official Tickers, IBES Tickers or CUSIP codes.
In order to collect data from IBES > Guidance > Detail History, I need to enter the IBES tickers.

I have seen earlier posts and other suggestions on how to merge these data-sets. However, I have only just become a bit familiar with SAS and I was not able to work out a solution using the suggested SAS script.

I would very much like to know if somebody would be able to help me or if there is a detailed description on how to get to a solution.

Thanks in advance!
Kind regards,
Thomas

Profile
 
 
Posted: 23 December 2015 07:12 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Thomas,

Can you tell a bit more? What was the SAS script that you tried and what part did you have trouble with?

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: 27 December 2015 09:39 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  4
Joined  2015-12-22

Hey Joost,

Thanks for your reply!

I have not extensively used SAS-Software before. However, I’m getting more familiar with the software by viewing the online tutorials and doing some example case studies.

I’m very confused on how to merge two datasets; Execucomp and IBES. My goal is to create an ‘Identifier Link Table’, with a unique identifier for each company,  that contains the corresponding identifiers (for Execucomp and IBES) for companies in the S&P 1500 for the period 2007 - 2014. I assume it is best to link and create identifiers for each year.

Currently, I have attained information on how to do this from two documents;

* This is a presentation from WRDS on merging datasets, however, it focuses on CRSP (instead of Execucomp) and IBES; http://wrds-web.wharton.upenn.edu/wrds/support/Additional Support/WRDS Presentations/_000user2007/linking_tables.pdf

* This is a SAS script from WRDS used to create a link table; http://wrds-web.wharton.upenn.edu/wrds/support/Data/_010Linking Databases/_000iclink.sas

I hope this helps to understand my problem.

Thanks in advance,
Kind regards,
Thomas

Profile
 
 
Posted: 27 December 2015 04:04 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Thomas,

See code here: https://gist.github.com/JoostUF/0e5a8ae738cc8ef14baf

This gets gvkey, permno and ibes ticker for all firms in Compustat, so getting only S&P 1500 firms needs to be added.

Let me know if you have any issues.

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: 28 December 2015 10:29 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  4
Joined  2015-12-22

Hey Joost,

Many thanks for your suggested SAS code, your help is much appreciated!
Currently, I’m doing e-Courses to get more familiar. However, thus far I have not been able to run the script successfully.
Here are some comments that perhaps clarify some of the challenges:


- I’m running the SAS University Edition using OS X and I’m not sure what, and how to edit this part;
/* determine file path (for relative paths) */
%let path= %sysfunc(tranwrd(%sysget(SAS_EXECFILEPATH),%sysget(SAS_EXECFILENAME),));


- I’m able to get access to WRDS through a daypass (which is actually valid for 14 days). This is a link that I can use with an enabled VPN-connection to the Erasmus University Rotterdam. I’m not sure if this works here (and how to edit this part);
/* sign on wrds */
%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;


- I have both CCI.sas file and the iclink.sas file stored in the same directory (/folders/myfolders/sasuser.v94/CCI.sas and /folders/myfolders/sasuser.v94/iclink.sas). I’m not sure how to edit the following part of the code;
/* upload iclink.sas (file iclink needs to be in same directory as this file) */
proc upload infile=”&path;.iclink.sas” outfile=’~/iclink.sas’; run;

/* execute iclink.sas (creates home.iclink) */
%include ‘~/iclink.sas’;


The Execucomp database contains data only on companies that are/have been in the S&P 1500. Therefore, I believe this would serve well as a parameter, as it includes only those companies that are listed in the S&P 1500 during a fiscal year. Do you have a suggestion on how to include this parameter?

Kind regards,
Thomas

Profile
 
 
Posted: 28 December 2015 03:45 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Thomas,

The path stuff can be replaced by full paths, e.g. : proc upload infile=”/folders/myfolders/sasuser.v94/iclink.sas” outfile=”~/iclink.sas”.

The signon part may not work with the vpn link. (There are 2 ways to use wrds: through their website, and through SAS with rsubmit; the vpn link may be just for the website)—probably best to check with the library.

Matching the resulting dataset (when everything works) with execucomp will take care of the SP1500. I suppose you would need to determine what exactly you need from Execucomp and then match it. See here for tables: https://wrds-web.wharton.upenn.edu/wrds//tools/variable.cfm?library_id=7 (scroll to COMP:EXECCOMP [ /wrds/comp/sasdata/execcomp ]).

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