Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Problem with mutiple dataset mergers

    Hi,

    These datasets are from WRDS from 2010-2017, One has director characteristics, directors compensation and firm financials. I am looking to merge these datasets.

    A common company ID across all three datasets are firm CUSIP and TICKER, the former is more accurate. Some of the CUSIP codes are 9 digits, in accrodance with WRDS I created a 6 (you can create a 8 digit code too) digit code accross all datasets.

    Code:
    gen cusip_6 = substr(cusip, 1, 6)
    The data is shown in a way where it makes 1:1, 1:M and M:1 not possible as the CUSIP's aren't unique. As soon as I reached and attempted M:M I thought of asking as there are clear warnings of the method yielding nothing fruitful (even though it worked and seemed to match everything with another dataset but can't be sure due to multiple warnings).

    I am currently trying to understand and attempt the -joinby- command.

    I do apologize if I missed something out from the FAQ's,. Thank you for your help!
    Data below is director characteristics (Riskmetrics/IRRC)


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str6 cusip_6 str30 ticker double year str100 name str34 ethnicity double(num_of_shares age)
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 84995 70
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                126846 68
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "AFRICAN-AMERICAN"          98755 66
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     94270 60
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 84523 68
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 52693 64
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                100996 56
"00846U" "A"  2010 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"               1737696 60
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 89930 71
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                121268 69
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "AFRICAN-AMERICAN"          89975 67
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     97125 61
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 76719  .
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 60425 65
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 94819 57
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "ASIAN"                         0 66
"00846U" "A"  2011 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"               1198693 62
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 89417 72
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                102328 70
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "BLACK/AFRICAN AMERICAN"    89462 68
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     98957 62
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 73648  .
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 66145 66
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 89516 58
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "ASIAN"                      4096 67
"00846U" "A"  2012 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                902379 63
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 42347 73
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 56138 71
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "BLACK/AFRICAN AMERICAN"    49687 69
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     91592 63
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 67500  .
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 28510 67
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 48942 59
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "ASIAN"                      8175 68
"00846U" "A"  2013 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"               1003052 64
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                128543 72
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "BLACK/AFRICAN AMERICAN"    49687 70
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 68284 72
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 28510 68
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 53192 60
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "ASIAN"                      8175 69
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"               1200729 65
"00846U" "A"  2014 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     68415 64
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                116573 69
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                128543 73
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                109799 61
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 82498 72
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     93913 65
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                501712 54
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "BLACK/AFRICAN AMERICAN"   113753 71
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  2353 67
"00846U" "A"  2015 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     23580 70
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 86136 69
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                133597 73
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                114629 61
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 77939 73
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     92055 65
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                634787 54
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  3562 62
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  2290 59
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  6672 67
"00846U" "A"  2016 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     30216 71
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 91778 69
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 92044 74
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 81826 62
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 74571 74
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     90184 66
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                556627 55
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  9660 63
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                  6994 59
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "CAUCASIAN"                 11385 68
"00846U" "A"  2017 "AGILENT TECHNOLOGIES, INC." "ASIAN"                     37523 71
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                111592 73
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                     0 63
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                 10000 58
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                 27355 64
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                 46778 69
"013817" "AA" 2010 "ALCOA INC."                 "HISPANIC"                  35241 59
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                 13640 64
"013817" "AA" 2010 "ALCOA INC."                 "HISPANIC"                  57207 57
"013817" "AA" 2010 "ALCOA INC."                 "AFRICAN-AMERICAN"          19434 59
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"                 17198 65
"013817" "AA" 2010 "ALCOA INC."                 "CAUCASIAN"               1459569 53
"013817" "AA" 2010 "ALCOA INC."                 "ASIAN"                     18961 73
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                  9154 64
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                 10000 59
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                 33742 64
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                 47170 70
"013817" "AA" 2011 "ALCOA INC."                 "HISPANIC"                  41677 60
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                 28211 65
"013817" "AA" 2011 "ALCOA INC."                 "AFRICAN-AMERICAN"          32204 60
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"                 27265 66
"013817" "AA" 2011 "ALCOA INC."                 "CAUCASIAN"               2035915 54
"013817" "AA" 2011 "ALCOA INC."                 "ASIAN"                     18961 75
"013817" "AA" 2012 "ALCOA INC."                 "CAUCASIAN"                 31510 65
"013817" "AA" 2012 "ALCOA INC."                 "CAUCASIAN"                 19547 60
"013817" "AA" 2012 "ALCOA INC."                 "CAUCASIAN"                 40928 66
"013817" "AA" 2012 "ALCOA INC."                 "CAUCASIAN"                 50136 71
"013817" "AA" 2012 "ALCOA INC."                 "HISPANIC/LATIN AMERICAN"   54452 61
"013817" "AA" 2012 "ALCOA INC."                 "CAUCASIAN"                 39065 66
end


    Director compensation (from execucomp on WRDS)

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str6 cusip_6 double year str50 coname str244 dirname double(cash_fees stock_awards)
"000361" 2010 "AAR CORP"                    "Peter Pace, USMC, Ret."         31.375   55.6
"000361" 2010 "AAR CORP"                    "Ronald R. Fogleman"              78.75  71.64
"000361" 2010 "AAR CORP"                    "Patrick J. Kelly"                 72.5  71.64
"000361" 2010 "AAR CORP"                    "Marc Jay Walfish"                   80  71.64
"000361" 2010 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       73.75  71.64
"000361" 2010 "AAR CORP"                    "Ronald B. Woodard"                82.5  71.64
"000361" 2010 "AAR CORP"                    "James E. Goodwin"                   85  71.64
"000361" 2010 "AAR CORP"                    "Michael R. Boyce"                78.75  71.64
"000361" 2010 "AAR CORP"                    "James Gilbert Brocksmith, Jr."   86.25  71.64
"000361" 2010 "AAR CORP"                    "Gerald F. Fitzgerald, Jr."      25.625  71.64
"000361" 2011 "AAR CORP"                    "Peter Pace, USMC, Ret."          76.25  128.9
"000361" 2011 "AAR CORP"                    "Ronald R. Fogleman"              83.75  128.9
"000361" 2011 "AAR CORP"                    "Patrick J. Kelly"                81.25  128.9
"000361" 2011 "AAR CORP"                    "Marc Jay Walfish"                 82.5  128.9
"000361" 2011 "AAR CORP"                    "Norman R. Bobins, BS, MBA"        87.5  128.9
"000361" 2011 "AAR CORP"                    "Ronald B. Woodard"               91.25  128.9
"000361" 2011 "AAR CORP"                    "James E. Goodwin"                91.25  128.9
"000361" 2011 "AAR CORP"                    "Michael R. Boyce"                78.75  128.9
"000361" 2011 "AAR CORP"                    "James Gilbert Brocksmith, Jr."   96.25  128.9
"000361" 2012 "AAR CORP"                    "Peter Pace, USMC, Ret."           77.5   57.8
"000361" 2012 "AAR CORP"                    "Ronald R. Fogleman"               87.5   57.8
"000361" 2012 "AAR CORP"                    "Patrick J. Kelly"                   85   57.8
"000361" 2012 "AAR CORP"                    "Marc Jay Walfish"                86.25   57.8
"000361" 2012 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       81.25   57.8
"000361" 2012 "AAR CORP"                    "Ronald B. Woodard"               91.25   57.8
"000361" 2012 "AAR CORP"                    "James E. Goodwin"                96.25   57.8
"000361" 2012 "AAR CORP"                    "Michael R. Boyce"                78.75   57.8
"000361" 2012 "AAR CORP"                    "Anthony K. Anderson"                60 54.628
"000361" 2012 "AAR CORP"                    "James Gilbert Brocksmith, Jr."    42.5   57.8
"000361" 2013 "AAR CORP"                    "Peter Pace, USMC, Ret."          78.75  103.4
"000361" 2013 "AAR CORP"                    "Ronald R. Fogleman"                120  103.4
"000361" 2013 "AAR CORP"                    "Patrick J. Kelly"                 82.5  103.4
"000361" 2013 "AAR CORP"                    "Marc Jay Walfish"                   80  103.4
"000361" 2013 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       86.25  103.4
"000361" 2013 "AAR CORP"                    "Ronald B. Woodard"               96.25  103.4
"000361" 2013 "AAR CORP"                    "James E. Goodwin"                 92.5  103.4
"000361" 2013 "AAR CORP"                    "Michael R. Boyce"                 72.5  103.4
"000361" 2013 "AAR CORP"                    "Anthony K. Anderson"             86.25  103.4
"000361" 2014 "AAR CORP"                    "Peter Pace, USMC, Ret."          81.25  122.5
"000361" 2014 "AAR CORP"                    "Ronald R. Fogleman"                120  122.5
"000361" 2014 "AAR CORP"                    "Patrick J. Kelly"                   85  122.5
"000361" 2014 "AAR CORP"                    "Marc Jay Walfish"                 87.5  122.5
"000361" 2014 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       88.75  122.5
"000361" 2014 "AAR CORP"                    "Ronald B. Woodard"               98.75  122.5
"000361" 2014 "AAR CORP"                    "James E. Goodwin"                96.25  122.5
"000361" 2014 "AAR CORP"                    "Michael R. Boyce"                 82.5  122.5
"000361" 2014 "AAR CORP"                    "Anthony K. Anderson"              87.5  122.5
"000361" 2015 "AAR CORP"                    "Peter Pace, USMC, Ret."          81.25  147.7
"000361" 2015 "AAR CORP"                    "Ronald R. Fogleman"              122.5  147.7
"000361" 2015 "AAR CORP"                    "Patrick J. Kelly"                   85  147.7
"000361" 2015 "AAR CORP"                    "Marc Jay Walfish"                 87.5  147.7
"000361" 2015 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       83.75  147.7
"000361" 2015 "AAR CORP"                    "Ronald B. Woodard"                97.5  147.7
"000361" 2015 "AAR CORP"                    "James E. Goodwin"                 97.5  147.7
"000361" 2015 "AAR CORP"                    "Michael R. Boyce"                81.25  147.7
"000361" 2015 "AAR CORP"                    "Anthony K. Anderson"              87.5  147.7
"000361" 2015 "AAR CORP"                    "Jennifer L. Vogel"              38.332 48.659
"000361" 2016 "AAR CORP"                    "Peter Pace, USMC, Ret."           87.5 121.95
"000361" 2016 "AAR CORP"                    "Ronald R. Fogleman"             123.75 121.95
"000361" 2016 "AAR CORP"                    "Patrick J. Kelly"                91.25 121.95
"000361" 2016 "AAR CORP"                    "Marc Jay Walfish"                   90 121.95
"000361" 2016 "AAR CORP"                    "Norman R. Bobins, BS, MBA"       88.75 121.95
"000361" 2016 "AAR CORP"                    "Ronald B. Woodard"                 100 121.95
"000361" 2016 "AAR CORP"                    "James E. Goodwin"                96.25 121.95
"000361" 2016 "AAR CORP"                    "Michael R. Boyce"                   80 121.95
"000361" 2016 "AAR CORP"                    "Anthony K. Anderson"             78.75 121.95
"000361" 2016 "AAR CORP"                    "Duncan J. McNabb"                   11 21.013
"000361" 2016 "AAR CORP"                    "Jennifer L. Vogel"                87.5 121.95
"000361" 2017 "AAR CORP"                    "Peter Pace, USMC, Ret."             80 174.75
"000361" 2017 "AAR CORP"                    "Ronald R. Fogleman"             45.641 174.75
"000361" 2017 "AAR CORP"                    "Patrick J. Kelly"                78.75 174.75
"000361" 2017 "AAR CORP"                    "Marc Jay Walfish"               92.651 174.75
"000361" 2017 "AAR CORP"                    "Norman R. Bobins, BS, MBA"          85 174.75
"000361" 2017 "AAR CORP"                    "Ronald B. Woodard"               96.25 174.75
"000361" 2017 "AAR CORP"                    "James E. Goodwin"              115.453 174.75
"000361" 2017 "AAR CORP"                    "Michael R. Boyce"                81.25 174.75
"000361" 2017 "AAR CORP"                    "Anthony K. Anderson"              72.5 174.75
"000361" 2017 "AAR CORP"                    "Duncan J. McNabb"                 82.5 174.75
"000361" 2017 "AAR CORP"                    "Jennifer L. Vogel"               81.25 174.75
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "David A. Roberts"                  103     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "Mickey P. Foret"               114.068     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "William R. Spivey"               191.5     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "Lois M. Martin"                107.952     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "Krish A. Prabhu, Ph.D."            114     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "Larry W. Wangberg"              110.75     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "John D. Wunsch"                    107     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "John E. Rehfeld"               117.051     70
"000886" 2010 "ADC TELECOMMUNICATIONS INC"  "John J. Boyle, III"                106     70
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Ray M. Robinson, Jr."               39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Judith Rodin"                       37 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Rajat Kumar Gupta"                  38 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Matthew K. Rose"                    39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Philip J. Purcell, III"             39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Roger T. Staubach"                  37 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "David L. Boren"                     39 18.949
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Michael A. Miles"                   39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Ann McLaughlin Korologos"           39 18.949
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "John W. Bachmann"                   39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Alberto Ibargen"                    39 24.103
"02376R" 2010 "AMERICAN AIRLINES GROUP INC" "Armando M. Codina"                  37 18.949
end

    and lastly Firm performance measures (from CRSP/Compustat on WRDS)

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str6 cusip_6 str8 tic double year str58 conm double(at sale mkvalt)
"000361" "AIR"    2009 "AAR CORP"                      1501.042  1352.151    777.8348
"000361" "AIR"    2010 "AAR CORP"                      1703.727  1775.782   1049.8206
"000361" "AIR"    2011 "AAR CORP"                      2195.653  2074.498    485.2897
"000361" "AIR"    2012 "AAR CORP"                        2136.9    2167.1    790.0029
"000361" "AIR"    2013 "AAR CORP"                        2199.5      2035     961.308
"000361" "AIR"    2014 "AAR CORP"                          1515    1594.3   1046.3954
"000361" "AIR"    2015 "AAR CORP"                        1442.1    1662.6    842.5112
"000361" "AIR"    2016 "AAR CORP"                        1504.1    1767.6   1200.3288
"000886" "ADCT.1" 2009 "ADC TELECOMMUNICATIONS INC"      1343.6     996.7     805.644
"000886" "ADCT.1" 2010 "ADC TELECOMMUNICATIONS INC"      1474.5    1156.6    1231.524
"02376R" "AAL"    2009 "AMERICAN AIRLINES GROUP INC"      25438     19917   2571.1835
"02376R" "AAL"    2010 "AMERICAN AIRLINES GROUP INC"      25088     22170   2597.5755
"02376R" "AAL"    2011 "AMERICAN AIRLINES GROUP INC"      23848     24022    117.3438
"02376R" "AAL"    2013 "AMERICAN AIRLINES GROUP INC"      42278     26712   6591.9923
"02376R" "AAL"    2014 "AMERICAN AIRLINES GROUP INC"      43771     42650  37405.5843
"02376R" "AAL"    2015 "AMERICAN AIRLINES GROUP INC"      48415     40990  26452.7417
"02376R" "AAL"    2016 "AMERICAN AIRLINES GROUP INC"      51274     40180  23685.5569
"02376R" "AAL"    2017 "AMERICAN AIRLINES GROUP INC"      51396     42207  24740.6812
"125141" "CECE"   2009 "CECO ENVIRONMENTAL CORP"         77.515   138.985     56.4416
"125141" "CECE"   2010 "CECO ENVIRONMENTAL CORP"         74.791   140.602     85.3412
"125141" "CECE"   2011 "CECO ENVIRONMENTAL CORP"         79.345   139.192     80.5638
"125141" "CECE"   2012 "CECO ENVIRONMENTAL CORP"         94.104   135.052    168.7421
"125141" "CECE"   2013 "CECO ENVIRONMENTAL CORP"        348.536   197.317    413.2812
"125141" "CECE"   2014 "CECO ENVIRONMENTAL CORP"        414.365   263.217    408.1892
"125141" "CECE"   2015 "CECO ENVIRONMENTAL CORP"        598.819   367.422    260.4902
"125141" "CECE"   2016 "CECO ENVIRONMENTAL CORP"        498.634   417.011    476.5599
"125141" "CECE"   2017 "CECO ENVIRONMENTAL CORP"        438.549   345.051    177.3441
"G3156P" "ASA"    2009 "ASA GOLD AND PRECIOUS METALS"   581.896     3.526     515.484
"G3156P" "ASA"    2010 "ASA GOLD AND PRECIOUS METALS"    671.58     4.956           .
"G3156P" "ASA"    2011 "ASA GOLD AND PRECIOUS METALS"   632.715     6.003           .
"G3156P" "ASA"    2012 "ASA GOLD AND PRECIOUS METALS"   468.013     5.817           .
"G3156P" "ASA"    2013 "ASA GOLD AND PRECIOUS METALS"   252.142     4.481           .
"G3156P" "ASA"    2014 "ASA GOLD AND PRECIOUS METALS"   223.333     2.219           .
"G3156P" "ASA"    2015 "ASA GOLD AND PRECIOUS METALS"    162.35     1.666           .
"G3156P" "ASA"    2016 "ASA GOLD AND PRECIOUS METALS"    245.02      1.21    208.5249
"G3156P" "ASA"    2017 "ASA GOLD AND PRECIOUS METALS"   245.562     1.408    213.2047
"002444" "AVX"    2009 "AVX CORP"                      2051.492  1304.966   2415.0508
"002444" "AVX"    2010 "AVX CORP"                      2319.482  1653.176   2536.8172
"002444" "AVX"    2011 "AVX CORP"                      2468.012  1545.254   2248.9093
"002444" "AVX"    2012 "AVX CORP"                      2601.995    1414.4   2006.7327
"002444" "AVX"    2013 "AVX CORP"                      2384.988  1442.604   2217.1528
"002444" "AVX"    2014 "AVX CORP"                      2459.015  1353.228   2400.0713
"002444" "AVX"    2015 "AVX CORP"                      2409.819  1195.529   2105.3744
"002444" "AVX"    2016 "AVX CORP"                      2477.413  1312.661   2750.6934
"002444" "AVX"    2017 "AVX CORP"                      2672.766  1562.474   2787.5827
"723484" "PNW"    2009 "PINNACLE WEST CAPITAL CORP"   11808.155  3297.101   3710.4923
"723484" "PNW"    2010 "PINNACLE WEST CAPITAL CORP"   12362.703  3263.645   4508.5165
"723484" "PNW"    2011 "PINNACLE WEST CAPITAL CORP"   13111.018  3241.379   5263.4723
"723484" "PNW"    2012 "PINNACLE WEST CAPITAL CORP"   13379.615  3301.804   5594.6981
"723484" "PNW"    2013 "PINNACLE WEST CAPITAL CORP"   13508.686  3454.628   5830.8314
"723484" "PNW"    2014 "PINNACLE WEST CAPITAL CORP"   14313.532  3491.632    7553.105
"723484" "PNW"    2015 "PINNACLE WEST CAPITAL CORP"   15028.258  3495.443   7155.9904
"723484" "PNW"    2016 "PINNACLE WEST CAPITAL CORP"   16004.253  3498.682   8687.6261
"723484" "PNW"    2017 "PINNACLE WEST CAPITAL CORP"   17019.082  3565.296   9519.0354
"002535" "AAN"    2009 "AARON'S INC"                   1321.456  1752.787   1463.4506
"002535" "AAN"    2009 "AARON'S INC"                   1321.456  1752.787   1463.4506
"002535" "AAN"    2010 "AARON'S INC"                   1502.072  1876.847   1632.9739
"002535" "AAN"    2011 "AARON'S INC"                   1735.149  2024.049   2018.0752
"002535" "AAN"    2012 "AARON'S INC"                   1812.929  2222.588   2141.3616
"002535" "AAN"    2013 "AARON'S INC"                   1827.176  2234.631   2144.9358
"002535" "AAN"    2014 "AARON'S INC"                   2456.844  2725.239   2215.9582
"002535" "AAN"    2015 "AARON'S INC"                   2658.875  3179.756    1625.514
"002535" "AAN"    2016 "AARON'S INC"                   2615.736  3207.716   2285.6535
"002535" "AAN"    2017 "AARON'S INC"                   2692.264  3383.708   2790.2572
"002824" "ABT"    2009 "ABBOTT LABORATORIES"          52416.623 30764.707  83747.5603
"002824" "ABT"    2010 "ABBOTT LABORATORIES"          59462.266 35166.721  74116.0034
"002824" "ABT"    2011 "ABBOTT LABORATORIES"          60276.893 38851.259  88302.4112
"002824" "ABT"    2012 "ABBOTT LABORATORIES"          67234.944  39873.91 103271.6885
"002824" "ABT"    2013 "ABBOTT LABORATORIES"              42953     21848  59338.5963
"002824" "ABT"    2014 "ABBOTT LABORATORIES"              41275     20247  67891.7357
"002824" "ABT"    2015 "ABBOTT LABORATORIES"              41247     20405  66137.3852
"002824" "ABT"    2016 "ABBOTT LABORATORIES"              52666     20853  56572.8983
"002824" "ABT"    2017 "ABBOTT LABORATORIES"              76250     27390  99507.3661
"81765M" "SERV.1" 2009 "SERVIDYNE INC"                   40.952    18.562     12.4988
"81765M" "SERV.1" 2010 "SERVIDYNE INC"                   26.228    26.158      8.5651
"004446" "ACETQ"  2010 "ACETO CORP"                     231.851   346.631     145.628
"004446" "ACETQ"  2011 "ACETO CORP"                     311.665   412.428    178.6202
"004446" "ACETQ"  2012 "ACETO CORP"                      299.28   444.388    243.2411
"004446" "ACETQ"  2013 "ACETO CORP"                      323.43    499.69    387.6858
"004446" "ACETQ"  2014 "ACETO CORP"                     467.984   510.179    521.9241
"004446" "ACETQ"  2015 "ACETO CORP"                     489.774   546.951    717.8906
"004446" "ACETQ"  2016 "ACETO CORP"                     540.778   558.524    647.8346
"004446" "ACETQ"  2017 "ACETO CORP"                    1038.185   638.318    464.9523
"004816" "ACU"    2009 "ACME UNITED CORP"                42.309    59.149     29.0514
"004816" "ACU"    2010 "ACME UNITED CORP"                49.581    63.149     29.2169
"004816" "ACU"    2011 "ACME UNITED CORP"                55.222    73.302     29.7825
"004816" "ACU"    2012 "ACME UNITED CORP"                67.828     84.37        34.5
"004816" "ACU"    2013 "ACME UNITED CORP"                68.079    89.577     47.6949
"004816" "ACU"    2014 "ACME UNITED CORP"                79.308   107.222     65.7871
"004816" "ACU"    2015 "ACME UNITED CORP"                81.421   109.812     58.2552
"004816" "ACU"    2016 "ACME UNITED CORP"                92.066   124.574     85.0203
"004816" "ACU"    2017 "ACME UNITED CORP"                114.73    130.55     78.9516
"05587G" "BKTI"   2009 "BK TECHNOLOGIES CORP"            31.421    27.989     42.1262
"05587G" "BKTI"   2010 "BK TECHNOLOGIES CORP"            34.792    25.954     24.3162
"05587G" "BKTI"   2011 "BK TECHNOLOGIES CORP"            31.847    24.104     14.8709
"05587G" "BKTI"   2012 "BK TECHNOLOGIES CORP"             33.96    27.576     22.7556
"05587G" "BKTI"   2013 "BK TECHNOLOGIES CORP"            34.023    27.023     45.7949
"05587G" "BKTI"   2014 "BK TECHNOLOGIES CORP"            36.986    30.971      65.592
"05587G" "BKTI"   2015 "BK TECHNOLOGIES CORP"            39.449    29.722     54.1001
"05587G" "BKTI"   2016 "BK TECHNOLOGIES CORP"            42.544    50.689     65.1938
end
    Last edited by Akash Mattoo; 02 Oct 2019, 10:07.
    Tags: merge
  • #2
    From my understanding, the first two data sets should have a unique identifier and you should be able to do a 1:1 match. For example observation #2 (Ronald R. Fogleman) should have a specific corresponding director characteristic (Caucasian, 77). I'm not familiar with WRDS but it might be worth your time to see if there is more info available to try and 1:1 match the 2 datasets. Maybe a more granular date that includes months or an individual ID?

    Once you've matched dataset 1 and 2 then its as simple as

    Code:
    use FirmPerformanceMeasures.dta, clear
merge 1:m cusip_6 year using CharacteristicsAndName.dta

    Comment

    • #3
      Hey! Thanks for the response! The issue is that these are datasets from WRDS (there are multiple information suppliers within WRDS). Director ID is not common or the same across Director characteristics and director compensation datasets (I will double check but did not come accross a director ID common between the two datasets), I have are CUSIP and TICKER codes that are common accross all three datasets that make merging possible.

      Comment

      Previous Next
      Working...
      X