/* The following program was written by Florio Arguillas to convert the 
Executive Compensation database distributed via Market Insight from Microsoft 
Access format to 8 SAS datasets with variable labels:

COLEV     Company level information
COMPTABL  Lists executives, their titles, and compensation data
COPERFTB  Textual footnotes
COPEROL   Items specific to each executive
LTAWDTAB  Long term incentive awards
PERSON    Executive identity and demographic information
STGRTTAB  Stock option grants

Access to Cornell's subscription to Market Insight is through the 
CUL Gateway and requires appropriate authentication.  The direct 
URL is

http://resolver.library.cornell.edu/misc/3949902

See the Market Insight help pages for downloading the entire
Execucomp database and for a list of detailed variable descriptions.

Please keep in mind that, in the future, the Executive Compensation database 
may change structure and format, requiring modifications to this program.  

pmb
9/21/2005*/

/*The first libname statement should be changed to the current
file name of the Executive Compensation mdb file downloaded from Market
Insight and the directory where it's located.  The second libname 
statement should reflect where you want to write the resulting SAS 
datasets.  These directories should not be the same.  Change netid
to your own directory.*/

LIBNAME mylib access 'U:\User4\netid\ec200506.mdb';
LIBNAME mylib1 'U:\User4\netid\EC';

DATA mylib1.coperol_Labeled;
SET mylib.coperol;
LABEL
CO_PER_ROL = "ID number for each executive/company combination "
PERMID = "Company ID number "
EXECID = "Executive ID number "
PCEO = "Current CEO "
BECAMECEO = "Date Became CEO "
JOINED_CO = "Date Joined Company "
REJOIN = "Date Rejoined Company "
LEFTOFC = "Date Left as CEO "
LEFTCO = "Date Left Company "
RELEFT = "Date Releft Company "
TITLE  = "Most recent Title "
REASON = "Reason Left Company "
EXECRANK = "Current Rank by Salary + Bonus ";
RUN;
PROC CONTENTS DATA = mylib1.coperol_Labeled;
RUN;

DATA mylib1.codirfin_Labeled;
SET mylib.codirfin;
LABEL
PERMID  = 'Company ID number '
YEAR  = 'Fiscal Year '
FYR  = 'Month of Fiscal Year End '
SRCDATE  = 'Source Date '
ANNDIRRET  = 'Annual Director Retainer '
DIRMTGFEE  = 'Director Meeting Fee '
NUMMTGS  = 'Number of Board Meetings '
PCOMMFEES  = 'Director Committee Meeting Fees Flag '
PEXECDIRPD  = 'Exeuctive Directors Receive Director Fees Flag '
PDIRPENSN  = 'Director Pension Plan Flag '
DIRSTK  = 'Annual Shares Received by All Directors '
DIRSTKAD  = 'Maximum Additional Shares (above DIRSTK)  received by some directors '
DIROPT  = 'Annual Options Received by All Directors '
DIROPTAD  = 'Maximum Additional Options (above DIROPT)  received by some directors '
SALES  = 'Sales '
SALECHG  = 'Sales 1 yr. Percent Change '
SALE3LS  = 'Sales 3 yr. Growth Rate (least squares) '
SALE5LS  = 'Sales 5 yr. Growth Rate (least squares) '
OIBD  = 'Operating Income Before Depreciation '
OIBDCHG  = 'OIBD 1 yr. Percent Change '
OIBD3LS  = 'OIBD 3 yr. Growth Rate (least squares) '
OIBD5LS  = 'OIBD 5 yr. Growth Rate (least squares) '
PRETAX  = 'Pretax Income '
NIBEX  = 'Net Income Before Extraordinary Items and Discontinued Operations '
NI  = 'Net Income (After Extraordinary Items and Discontinued Operations) '
NICHG  = 'Net Income 1 yr. Percent Change '
NI3LS  = 'Net Income 3 yr. Growth Rate (least squares) '
NI5LS  = 'Net Income 5 yr. Growth Rate (least squares) '
NIAC  = 'Net Income (Before Extra. Items) less Preferred Dividend Requirements '
EPSEX  = 'Earnings Per Share (Excl. Extraordinary Items and Discontiued Operations) '
EPSEXCHG  = 'EPS 1 yr. Percent Change '
EPSEX3LS  = 'EPS 3 yr. Growth Rate (least squares) '
EPSEX5LS  = 'EPS 5 yr. Growth Rate (least squares) '
EPSIN  = 'Earnings Per Shares (Included Extraordinary Items and Discontinued Operations) '
EPSINCHG  = 'EPS (Incl. Extra & Disc) 1 yr. Percent Change '
COMMEQ  = 'Common Equity '
SEQ  = 'Stockholders Equity '
ROEPER  = 'Return on Equity '
ROEAVG  = 'Return on Average Equity '
ASSETS  = 'Assets '
ASSETCHG  = 'Assets 1 yr. Percent Change '
ROA  = 'Return on Assets '
MKTVAL  = 'Market Value (Fiscal-Year End) '
DIVYIELD  = 'Dividend Yield (Fiscal-Year End) '
PRCCF  = 'Close Price (Fiscal-Year End) '
PRCC  = 'Close Price (Calendar-Year End) '
SHRSOUT  = 'Common Shares Outstanding '
TRS1YR  = '1 Yr. Total Return to Shareholders (Dividends Reinvested) '
TRS3YR  = '3 Yr. Total Return to Shareholders (Dividends Reinvested) '
TRS5YR  = '5 Yr. Total Return to Shareholders (Dividneds Reinvested) '
EMPL  = 'Employees (# - thousands) '
AJEX  = 'Adjustment Factor for Stock Splits '
BS_VOLATILITY  = 'Volatility (60 month) used to calculate Black-Scholes values '
BS_YIELD  = 'Dividend Yield (3 year average) used to calculate Black-Scholes values ';
RUN;
PROC CONTENTS DATA = mylib1.codirfin_Labeled;
RUN;

DATA mylib1.colev_Labeled;
SET mylib.colev;
LABEL
PERMID  = 'Company ID number '
CONAME  = 'Company Name '
TICKER  = 'Ticker Symbol '
EXCHANGE  = 'Stock Exchange Company Trades on '
INDDESC  = "Company's Industry Group (text) "
SPINDEX  = "Company's Industry Group (code) "
CUSIP  = 'CUSIP and Issue Number '
ADDRESS  = 'Street Address '
CITY  = 'City '
STATE  = 'State '
ZIP  = 'Zip Code '
SUB_TELE  = 'Area Code '
TELE  = 'Telephone Number '
SPCODE  = 'S&P Index (S&P 500, Midcap, Smallcap) '
SIC  = 'SIC Code '
SICDESC  = 'Description of SIC Code. '
NAICS  = 'NAICS Code '
NAICSDESC  = 'Description of NAICS Code ';
RUN;

PROC CONTENTS DATA = mylib1.colev_Labeled;
RUN;

DATA mylib1.comptabl_Labeled;
SET mylib.comptabl;
LABEL
CO_PER_ROL  = 'ID number for each executive/company combination '
YEAR  = 'Fiscal Year '
SALARY  = 'Salary '
BONUS  = 'Bonus '
OTHANN  = 'Other Annual '
TCC  = 'Total Current Compensation (Salary + Bonus) '
TDC1  = 'Total Compensation (Salary + Bonus + Other Annual + Restriced Stock Grants + LTIP Payouts + All Other + Value of Option Grants) '
TDC2  = 'Total Compensation (Salary + Bonus + Other Annual + Restriced Stock Grants + LTIP Payouts + All Other + Value of Options Exercised) '
RSTKGRNT  = 'Restricted Stock Grant ($) '
RSTKVYRS  = 'Years Until Restricted Stock Grant Begins to Vest (if <3) '
RSTKHLD  = 'Restricted Stock Holdings (# shares) '
RSTKHLDV  = 'Restricted Stock Holdings ($ value) '
SOPTGRNT  = 'Options Granted (#) '
BLK_VALUE  = 'Options Granted ($ - Black Scholes value) '
LTIP  = 'LTIP Payouts '
ALLOTHTOT  = 'All Other Total '
ALLOTHPD  = 'All Other Paid '
SOPTEXSH  = 'Options Exercised (#) '
SOPTEXER  = 'Value Realized from Options Exercised '
UEXNUMEX  = 'Unexercised Exercisable Options (#) '
UEXNUMUN  = 'Unexercised Unexercisable Options (#) '
INMONEX  = 'Unexercised Exercisable Options ($) '
INMONUN  = 'Unexercised Unexercisable Options ($) '
SHROWN  = 'Shares Owned (excl. options) '
SHROWNPC  = 'Percentage of Company Stock Held by Executive '
SAL_PCT  = 'Salary Percent Change Year-to-Year '
TCC_PCT  = 'TCC Percent Change Year-to-Year '
TDC1_PCT  = 'TDC1 Percent Change Year-to-Year '
TDC2_PCT  = 'TDC2 Percent Change Year-to-Year '
PINCLOPT   = 'Shares Owned Includes Options Flag '
SOPTVAL  = 'Options Granted ($ - as valued by company) '
CEOANN  = 'Annual CEO Flag '
TITLEANN  = 'Annual Title ';
RUN;
PROC CONTENTS DATA = mylib1.comptabl_Labeled;
RUN;

DATA mylib1.coperftb_Labeled;
SET mylib.coperftb;
LABEL
CO_PER_ROL  = 'ID number for each executive/company combination '
YEAR  = 'Fiscal Year '
COMMENT  = 'Footnote Text '
PINTRLOCK  = 'Interlock Flag '
RETYRS  = 'Retirement Years '
PREPRICE  = 'Reprice Flag '
STFN  = 'Short-Term Footnote Flag '
LTFN  = 'Long-Term Footnote Flag '
PEXECDIR  = 'Executive is Director ';
RUN;
PROC CONTENTS DATA = mylib1.coperftb_Labeled;
RUN;

DATA mylib1.ltawdtab_Labeled;
SET mylib.ltawdtab;
LABEL
CO_PER_ROL  = 'ID number for each executive/company combination '
YEAR  = 'Fiscal Year '
AWDNUM  = 'Award Number identifier '
NUMBER  = 'Number of Units Awarded '
PERIOD  = 'Performance Period (years) '
VALTHRES  = 'Threshold Future Payout ($) '
VALTARG  = 'Target Future Payout ($) '
VALMAX  = 'Maximum Future Payout ($) '
SHRTHRES  = 'Threshold Future Payout (# shares) '
SHRTARG  = 'Target Future Payout (# shares) '
SHRMAX  = 'Maximum Future Payout (# shares) ';
RUN;
PROC CONTENTS DATA = mylib1.ltawdtab_Labeled;
RUN;

DATA mylib1.stgrttab_Labeled;
SET mylib.stgrttab;
LABEL
CO_PER_ROL  = 'ID number for each executive/company combination. '
YEAR  = 'Fiscal Year '
GRNTNUM  = 'Grant Number identifier '
PRELOAD  = 'Reload Flag '
NUMSECUR  = 'Number of Options Granted '
PCTTOTOPT  = 'Percent of Total Options Granted to All Employees '
MKTPRIC  = 'Market Price of Stock on Date of Grant '
EXPRIC  = 'Exercise Price '
EXDATE  = 'Expiration Date '
PCDVALUE  = 'Company Valuation Method (Present or 5 Percent) '
VALUE  = 'Value (Company) '
BLKSHVAL  = 'Value (Black-Scholes) ';
RUN;
PROC CONTENTS DATA = mylib1.stgrttab_Labeled;
RUN;

DATA mylib1.person_Labeled;
SET mylib.person;
LABEL
EXECID  = 'Executive ID number '
EXEC_LNAME  = 'Last Name '
EXEC_FNAME  = 'First Name '
EXEC_MNAME  = 'Middle Name '
P_AGE_2  = 'Age '
PGENDER  = 'Gender '
NAMEPREFIX  = 'Name Prefix (Mr. or Ms.) ';
RUN;
PROC CONTENTS DATA = mylib1.person_Labeled;
RUN;