Announcement

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

    Query about merge files in different format

    Hi, I am wondering how to merge two databases which are in different format? Thank you so much for your answer.
    Please kindly check the following two database:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double year str10 cusip str26 event_type
"126555" 2010 "01167P101" "Extreme Cold/Wind Chill"
"023450" 2010 "666762109" "Blizzard"               
"121853" 2010 "011757101" "Winter Storm"           
"121853" 2010 "011757101" "Blizzard"               
"023450" 2010 "666762109" "Ice Storm"              
"121853" 2011 "011757101" "Blizzard"               
"121853" 2011 "011757101" "Coastal Flood"          
"023450" 2011 "666762109" "Winter Storm"           
"126555" 2011 "01167P101" "Blizzard"               
"023450" 2011 "666762109" "Blizzard"               
"126555" 2012 "01167P101" "High Wind"              
"018195" 2012 "32112J106" "Extreme Cold/Wind Chill"
"023450" 2012 "666762109" "Extreme Cold/Wind Chill"
"023450" 2012 "666762109" "Blizzard"               
"121853" 2012 "011757101" "Flood"                  
"121853" 2012 "011757101" "Winter Storm"           
"018195" 2012 "32112J106" "Extreme Cold/Wind Chill"
"023450" 2013 "666762109" "Blizzard"               
"018195" 2013 "32112J106" "Blizzard"               
"121853" 2013 "011757101" "Blizzard"               
"023450" 2013 "666762109" "High Wind"              
"018195" 2013 "32112J106" "Winter Storm"           
"126555" 2013 "01167P101" "Frost/Freeze"           
"121853" 2013 "011757101" "Winter Storm"           
"023450" 2014 "666762109" "Blizzard"               
"023450" 2014 "666762109" "Blizzard"               
"126555" 2014 "01167P101" "Winter Storm"           
"018195" 2014 "32112J106" "Blizzard"               
"018195" 2014 "32112J106" "Flood"                  
"126555" 2015 "01167P101" "Strong Wind"            
"018195" 2015 "32112J106" "Blizzard"               
"018195" 2015 "32112J106" "Heavy Snow"             
"023450" 2015 "666762109" "Blizzard"               
"023450" 2015 "666762109" "Flood"                  
"018195" 2016 "32112J106" "Blizzard"               
"023450" 2016 "666762109" "Blizzard"               
"023450" 2016 "666762109" "Blizzard"               
"126555" 2016 "01167P101" "Flood"                  
"018195" 2016 "32112J106" "High Wind"              
"023450" 2017 "666762109" "Blizzard"               
"126555" 2017 "01167P101" "Winter Storm"           
"023450" 2017 "666762109" "Blizzard"               
"018195" 2017 "32112J106" "Blizzard"               
"018195" 2017 "32112J106" "Blizzard"               
"126555" 2018 "01167P101" "Blizzard"               
"018195" 2018 "32112J106" "Frost/Freeze"           
"023450" 2018 "666762109" "Winter Storm"           
"018195" 2018 "32112J106" "Winter Storm"           
"023450" 2018 "666762109" "Winter Storm"           
"023450" 2019 "666762109" "Winter Storm"           
"023450" 2019 "666762109" "Blizzard"               
"018195" 2019 "32112J106" "Heavy Snow"             
"126555" 2019 "01167P101" "Heavy Snow"             
"018195" 2019 "32112J106" "Heavy Snow"             
"018195" 2020 "32112J106" "Heavy Snow"             
"126555" 2020 "01167P101" "Cold/Wind Chill"        
"023450" 2020 "666762109" "Astronomical Low Tide"  
"023450" 2020 "666762109" "Heavy Snow"             
"018195" 2020 "32112J106" "Flood"                  
"110065" 2010 "45665Q103" "Hail"                   
"028900" 2010 "195872106" "Hail"                   
"004674" 2010 "7591EP100" "Thunderstorm Wind"      
"162160" 2010 "58463J304" "Flash Flood"            
"024440" 2010 "74267C106" "Thunderstorm Wind"      
"028900" 2010 "195872106" "Heavy Snow"             
"029055" 2010 "895925105" "Thunderstorm Wind"      
"016829" 2010 "722903101" "Heavy Snow"             
"006115" 2010 "460321201" "Drought"                
"001224" 2010 "010284008" "Thunderstorm Wind"      
"011228" 2010 "929160109" "Thunderstorm Wind"      
"008789" 2010 "743674103" "Thunderstorm Wind"      
"015274" 2010 "93317Q105" "Hail"                   
"061394" 2010 "842233108" "Thunderstorm Wind"      
"017072" 2010 "637546102" "Hail"                   
"004674" 2010 "7591EP100" "Hail"                   
"066597" 2010 "33744V103" "Thunderstorm Wind"      
"061055" 2010 "050473107" "Hail"                   
"030576" 2010 "00738A106" "Hail"                   
"066597" 2010 "33744V103" "Thunderstorm Wind"      
"010783" 2010 "90136P003" "Hail"                   
"005210" 2010 "381010107" "Thunderstorm Wind"      
"025896" 2010 "098570104" "Lightning"              
"001225" 2010 "010392009" "Thunderstorm Wind"      
"162160" 2010 "58463J304" "Heavy Rain"             
"016829" 2010 "722903101" "Tornado"                
"061394" 2010 "842233108" "Hail"                   
"008789" 2010 "743674103" "Thunderstorm Wind"      
"061055" 2010 "050473107" "Tornado"                
"019049" 2010 "05978R107" "Thunderstorm Wind"      
"147849" 2010 "205306103" "Hail"                   
"029055" 2010 "895925105" "Thunderstorm Wind"      
"184275" 2010 "844027102" "Heat"                   
"019439" 2010 "483059101" "Hail"                   
"183663" 2010 "230153108" "Thunderstorm Wind"      
"110065" 2010 "45665Q103" "Hail"                   
"186721" 2010 "853616209" "Thunderstorm Wind"      
"019049" 2010 "05978R107" "Flash Flood"            
"004351" 2010 "29265N108" "Thunderstorm Wind"      
"012597" 2010 "511795106" "Lightning"              
"012589" 2010 "29261A100" "Hail"                   
end
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str25 cusip str21 esg_2010 str8(esg_2011 esg_2012 esg_2013 esg_2014 esg_2015 esg_2016 esg_2017 esg_2018) float(esg_2019 esg_2020)
"04208V103"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04208V202"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04208V111"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00032Q104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02376R102"             "40.4959"               "41.7355"  "41.3223"  "29.7521"  "43.3884"  "45.8678"  "47.5207"  "50.8264" "54.1322" 56.6116 56.6116
"048209100"             "11.8421"               "11.8421"  "11.8421"  "11.8421"  "11.8421"  "11.8421"  "12.7193"  "17.1053" "17.1053" 17.1053 17.1053
"03823U102"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "14.876"   "14.876"   "14.876"  "14.876"   14.876  14.876
"000360206"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "16.5289" "19.0083" 21.9008 21.9008
"037833100"             "43.5407"               "42.1053"  "45.933"   "46.89"    "49.7608"  "53.11"    "53.11"    "54.5455" "55.0239" 55.0239       .
"053306106"             "13.2231"               "13.2231"  "16.5289"  "16.5289"  "16.5289"  "16.5289"  "16.5289"  "16.5289" "16.5289" 13.2231 16.5289
"049164205"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "14.876"   "14.876"   "14.876"  "16.1157" 23.1405 23.1405
"03076K108"             "11.8421"               "11.8421"  "11.8421"  "11.8421"  "11.8421"  "11.8421"  "12.7193"  "19.2982" "19.2982" 17.9825 23.2456
"00289Y107"             "12.8099"               "12.8099"  "12.8099"  "12.8099"  "12.8099"  "12.8099"  "15.2893"  "15.2893" "15.2893" 15.2893 15.2893
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00211Y506"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"003654100"             "11.157"                "11.157"   "11.157"   "11.157"   "16.5289"  "16.5289"  "16.5289"  "16.5289" "16.5289" 17.3554       .
"009066101"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00509G209"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00091E109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"01748H107"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "14.9123"  "15.7895"  "18.4211" "18.4211" 18.4211 21.9298
"00091F106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"004225108"             "14.0496"               "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496" "15.2893" 15.2893 14.0496
"048453104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048453203"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048453112"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048269203"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.8421"  "11.8421"  "18.4211" "20.614"   20.614  20.614
"00437E102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00444P108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.9835"  "10.7438"  "14.0496" "14.876"   14.876  14.876
"007002108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.5702" "14.876"   14.876  14.876
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00404A109"             "#N/A N/A"              "10.3306"  "11.157"   "9.9174"   "9.9174"   "13.6364"  "13.6364"  "18.1818" "19.4215" 19.4215 19.4215
"004468500"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "14.0496"  "16.1157" "16.1157" 16.1157 16.1157
"004498101"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "14.0496"  "14.0496"  "16.5289" "16.5289" 15.2893 20.6612
"00461L303"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461L204"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461L113"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"054540208"             "13.2231"               "13.2231"  "13.2231"  "13.2231"  "13.2231"  "13.2231"  "14.876"   "16.1157" "16.1157" 21.9008 30.1653
"00108J109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"000868109"             "12.7193"               "12.7193"  "12.7193"  "11.4035"  "12.7193"  "11.4035"  "12.7193"  "15.7895" "16.6667" 16.6667 16.6667
"00484M601"             "9.9174"                "9.9174"   "9.9174"   "9.9174"   "11.157"   "16.1157"  "16.1157"  "16.1157" "16.1157" 16.1157 16.1157
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461U105"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "16.1157" "16.1157" 16.1157 16.1157
"00444T100"             "7.8512"                "11.9835"  "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "15.2893" "15.2893" 15.2893 15.2893
"29249E109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"003881307"             "11.157"                "11.157"   "9.9174"   "11.157"   "11.157"   "11.157"   "11.157"   "14.0496" "9.0909"  12.8099 14.0496
"00091G104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00510M104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00724F101"             "42.9752"               "46.6942"  "47.9339"  "46.6942"  "46.6942"  "47.1074"  "48.3471"  "54.9587" "54.9587" 54.9587       .
"90138P100"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"90138P209"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"90138P118"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00770C101"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "12.8099"  "14.876"  "14.876"   14.876       .
"00534A102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"032654105"             "40.4959"               "41.7355"  "40.9091"  "33.8843"  "41.7355"  "42.9752"  "39.2562"  "38.0165" "44.2149" 48.7603       .
"00688A106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00688A114"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"000899104"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.9835"  "14.0496"  "14.0496" "14.0496" 14.0496 14.0496
"00547W208"             "11.157"                "11.157"   "11.157"   "10.3306"  "11.157"   "11.157"   "14.0496"  "14.0496" "14.0496" 14.0496       .
"00548A106"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "7.8512"   "11.157"   "11.157"   "11.157"   "14.876"  "14.876"   14.876 13.6364
"00788A105"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00788A113"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"053015103"             "14.876"                "17.3554"  "17.3554"  "18.1818"  "20.2479"  "21.0744"  "23.5537"  "30.9917" "43.8017" 33.4711       .
"00650F109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"052769106"             "48.7603"               "53.3058"  "52.4793"  "52.8926"  "51.2397"  "50.8264"  "50.8264"  "48.7603" "47.5207" 47.5207       .
"00738A106"             "11.157"                "11.157"   "9.9174"   "11.157"   "11.157"   "11.157"   "11.157"   "19.8347" "37.1901" 25.2066 25.2066
"007025109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"006739106"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "16.1157" "16.1157" 15.2893 15.2893
"00791N102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00773U108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "11.157"   "16.5289" "16.5289" 16.5289 16.5289
"00791N110"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"007624307"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "14.4628"  "15.2893" "15.2893" 15.2893       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02157M207"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L205"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L114"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00760J108"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02116A104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"007973100"             "11.157"                "11.157"   "11.157"   "11.157"   "14.876"   "14.876"   "14.876"   "17.3554" "17.3554" 18.1818 23.1405
"00808Y307"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"025537101"             "47.9245"               "54.717"   "45.6604"  "52.8302"  "51.3208"  "51.6981"  "58.8679"  "58.8679" "59.2453" 57.7358 57.3585
"#N/A Invalid Security" "#N/A Invalid Security" ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A Invalid Security" "#N/A Invalid Security" ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00771V108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "10.3306"  "11.157"   "14.0496"  "14.0496"  "17.3554" "17.3554" 17.3554 21.9008
"019170109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"006743306"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"050734201"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04005A203"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
end
    Tags: None
  • #2
    So the second data set is a mess, and if you were to try to directly combine it with the first one, the clash between long and wide layouts would give you a data set that would be impossible to use. The second data set has to be cleaned up and then converted to long layout.

    Many of the observations in the second data set are duplicates, and, in fact, they are duplicates that all have #N/A cusip and missing values in the esg variables. So those observations are useless and need to be dropped. Next, the esg variables are intended to be numbers, but, because they often contain "#N/A" they were read by Stata as strings. If all of them were strings, that would be inconvenient but not a real problem. But since the last several are numeric, that blocks -reshape-. So they need to be -destring-ed first.

    So in the end, the following code should do it:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double year str10 cusip str26 event_type
"126555" 2010 "01167P101" "Extreme Cold/Wind Chill"
"023450" 2010 "666762109" "Blizzard"               
"121853" 2010 "011757101" "Winter Storm"           
"121853" 2010 "011757101" "Blizzard"               
"023450" 2010 "666762109" "Ice Storm"              
"121853" 2011 "011757101" "Blizzard"               
"121853" 2011 "011757101" "Coastal Flood"          
"023450" 2011 "666762109" "Winter Storm"           
"126555" 2011 "01167P101" "Blizzard"               
"023450" 2011 "666762109" "Blizzard"               
"126555" 2012 "01167P101" "High Wind"              
"018195" 2012 "32112J106" "Extreme Cold/Wind Chill"
"023450" 2012 "666762109" "Extreme Cold/Wind Chill"
"023450" 2012 "666762109" "Blizzard"               
"121853" 2012 "011757101" "Flood"                  
"121853" 2012 "011757101" "Winter Storm"           
"018195" 2012 "32112J106" "Extreme Cold/Wind Chill"
"023450" 2013 "666762109" "Blizzard"               
"018195" 2013 "32112J106" "Blizzard"               
"121853" 2013 "011757101" "Blizzard"               
"023450" 2013 "666762109" "High Wind"              
"018195" 2013 "32112J106" "Winter Storm"           
"126555" 2013 "01167P101" "Frost/Freeze"           
"121853" 2013 "011757101" "Winter Storm"           
"023450" 2014 "666762109" "Blizzard"               
"023450" 2014 "666762109" "Blizzard"               
"126555" 2014 "01167P101" "Winter Storm"           
"018195" 2014 "32112J106" "Blizzard"               
"018195" 2014 "32112J106" "Flood"                  
"126555" 2015 "01167P101" "Strong Wind"            
"018195" 2015 "32112J106" "Blizzard"               
"018195" 2015 "32112J106" "Heavy Snow"             
"023450" 2015 "666762109" "Blizzard"               
"023450" 2015 "666762109" "Flood"                  
"018195" 2016 "32112J106" "Blizzard"               
"023450" 2016 "666762109" "Blizzard"               
"023450" 2016 "666762109" "Blizzard"               
"126555" 2016 "01167P101" "Flood"                  
"018195" 2016 "32112J106" "High Wind"              
"023450" 2017 "666762109" "Blizzard"               
"126555" 2017 "01167P101" "Winter Storm"           
"023450" 2017 "666762109" "Blizzard"               
"018195" 2017 "32112J106" "Blizzard"               
"018195" 2017 "32112J106" "Blizzard"               
"126555" 2018 "01167P101" "Blizzard"               
"018195" 2018 "32112J106" "Frost/Freeze"           
"023450" 2018 "666762109" "Winter Storm"           
"018195" 2018 "32112J106" "Winter Storm"           
"023450" 2018 "666762109" "Winter Storm"           
"023450" 2019 "666762109" "Winter Storm"           
"023450" 2019 "666762109" "Blizzard"               
"018195" 2019 "32112J106" "Heavy Snow"             
"126555" 2019 "01167P101" "Heavy Snow"             
"018195" 2019 "32112J106" "Heavy Snow"             
"018195" 2020 "32112J106" "Heavy Snow"             
"126555" 2020 "01167P101" "Cold/Wind Chill"        
"023450" 2020 "666762109" "Astronomical Low Tide"  
"023450" 2020 "666762109" "Heavy Snow"             
"018195" 2020 "32112J106" "Flood"                  
"110065" 2010 "45665Q103" "Hail"                   
"028900" 2010 "195872106" "Hail"                   
"004674" 2010 "7591EP100" "Thunderstorm Wind"      
"162160" 2010 "58463J304" "Flash Flood"            
"024440" 2010 "74267C106" "Thunderstorm Wind"      
"028900" 2010 "195872106" "Heavy Snow"             
"029055" 2010 "895925105" "Thunderstorm Wind"      
"016829" 2010 "722903101" "Heavy Snow"             
"006115" 2010 "460321201" "Drought"                
"001224" 2010 "010284008" "Thunderstorm Wind"      
"011228" 2010 "929160109" "Thunderstorm Wind"      
"008789" 2010 "743674103" "Thunderstorm Wind"      
"015274" 2010 "93317Q105" "Hail"                   
"061394" 2010 "842233108" "Thunderstorm Wind"      
"017072" 2010 "637546102" "Hail"                   
"004674" 2010 "7591EP100" "Hail"                   
"066597" 2010 "33744V103" "Thunderstorm Wind"      
"061055" 2010 "050473107" "Hail"                   
"030576" 2010 "00738A106" "Hail"                   
"066597" 2010 "33744V103" "Thunderstorm Wind"      
"010783" 2010 "90136P003" "Hail"                   
"005210" 2010 "381010107" "Thunderstorm Wind"      
"025896" 2010 "098570104" "Lightning"              
"001225" 2010 "010392009" "Thunderstorm Wind"      
"162160" 2010 "58463J304" "Heavy Rain"             
"016829" 2010 "722903101" "Tornado"                
"061394" 2010 "842233108" "Hail"                   
"008789" 2010 "743674103" "Thunderstorm Wind"      
"061055" 2010 "050473107" "Tornado"                
"019049" 2010 "05978R107" "Thunderstorm Wind"      
"147849" 2010 "205306103" "Hail"                   
"029055" 2010 "895925105" "Thunderstorm Wind"      
"184275" 2010 "844027102" "Heat"                   
"019439" 2010 "483059101" "Hail"                   
"183663" 2010 "230153108" "Thunderstorm Wind"      
"110065" 2010 "45665Q103" "Hail"                   
"186721" 2010 "853616209" "Thunderstorm Wind"      
"019049" 2010 "05978R107" "Flash Flood"            
"004351" 2010 "29265N108" "Thunderstorm Wind"      
"012597" 2010 "511795106" "Lightning"              
"012589" 2010 "29261A100" "Hail"                   
end

tempfile master
save `master'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str25 cusip str21 esg_2010 str8(esg_2011 esg_2012 esg_2013 esg_2014 esg_2015 esg_2016 esg_2017 esg_2018) float(esg_2019 esg_2020)
"04208V103"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04208V202"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04208V111"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00032Q104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02376R102"             "40.4959"               "41.7355"  "41.3223"  "29.7521"  "43.3884"  "45.8678"  "47.5207"  "50.8264" "54.1322" 56.6116 56.6116
"048209100"             "11.8421"               "11.8421"  "11.8421"  "11.8421"  "11.8421"  "11.8421"  "12.7193"  "17.1053" "17.1053" 17.1053 17.1053
"03823U102"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "14.876"   "14.876"   "14.876"  "14.876"   14.876  14.876
"000360206"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "16.5289" "19.0083" 21.9008 21.9008
"037833100"             "43.5407"               "42.1053"  "45.933"   "46.89"    "49.7608"  "53.11"    "53.11"    "54.5455" "55.0239" 55.0239       .
"053306106"             "13.2231"               "13.2231"  "16.5289"  "16.5289"  "16.5289"  "16.5289"  "16.5289"  "16.5289" "16.5289" 13.2231 16.5289
"049164205"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "14.876"   "14.876"   "14.876"  "16.1157" 23.1405 23.1405
"03076K108"             "11.8421"               "11.8421"  "11.8421"  "11.8421"  "11.8421"  "11.8421"  "12.7193"  "19.2982" "19.2982" 17.9825 23.2456
"00289Y107"             "12.8099"               "12.8099"  "12.8099"  "12.8099"  "12.8099"  "12.8099"  "15.2893"  "15.2893" "15.2893" 15.2893 15.2893
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00211Y506"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"003654100"             "11.157"                "11.157"   "11.157"   "11.157"   "16.5289"  "16.5289"  "16.5289"  "16.5289" "16.5289" 17.3554       .
"009066101"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00509G209"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00091E109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"01748H107"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "14.9123"  "15.7895"  "18.4211" "18.4211" 18.4211 21.9298
"00091F106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"004225108"             "14.0496"               "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496"  "14.0496" "15.2893" 15.2893 14.0496
"048453104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048453203"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048453112"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"048269203"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.8421"  "11.8421"  "18.4211" "20.614"   20.614  20.614
"00437E102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00444P108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.9835"  "10.7438"  "14.0496" "14.876"   14.876  14.876
"007002108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.5702" "14.876"   14.876  14.876
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00404A109"             "#N/A N/A"              "10.3306"  "11.157"   "9.9174"   "9.9174"   "13.6364"  "13.6364"  "18.1818" "19.4215" 19.4215 19.4215
"004468500"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "14.0496"  "16.1157" "16.1157" 16.1157 16.1157
"004498101"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "14.0496"  "14.0496"  "16.5289" "16.5289" 15.2893 20.6612
"00461L303"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461L204"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461L113"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"054540208"             "13.2231"               "13.2231"  "13.2231"  "13.2231"  "13.2231"  "13.2231"  "14.876"   "16.1157" "16.1157" 21.9008 30.1653
"00108J109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"000868109"             "12.7193"               "12.7193"  "12.7193"  "11.4035"  "12.7193"  "11.4035"  "12.7193"  "15.7895" "16.6667" 16.6667 16.6667
"00484M601"             "9.9174"                "9.9174"   "9.9174"   "9.9174"   "11.157"   "16.1157"  "16.1157"  "16.1157" "16.1157" 16.1157 16.1157
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00461U105"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "16.1157" "16.1157" 16.1157 16.1157
"00444T100"             "7.8512"                "11.9835"  "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "15.2893" "15.2893" 15.2893 15.2893
"29249E109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"003881307"             "11.157"                "11.157"   "9.9174"   "11.157"   "11.157"   "11.157"   "11.157"   "14.0496" "9.0909"  12.8099 14.0496
"00091G104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00510M104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00724F101"             "42.9752"               "46.6942"  "47.9339"  "46.6942"  "46.6942"  "47.1074"  "48.3471"  "54.9587" "54.9587" 54.9587       .
"90138P100"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"90138P209"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"90138P118"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00770C101"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "12.8099"  "14.876"  "14.876"   14.876       .
"00534A102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"032654105"             "40.4959"               "41.7355"  "40.9091"  "33.8843"  "41.7355"  "42.9752"  "39.2562"  "38.0165" "44.2149" 48.7603       .
"00688A106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00688A114"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"000899104"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.9835"  "14.0496"  "14.0496" "14.0496" 14.0496 14.0496
"00547W208"             "11.157"                "11.157"   "11.157"   "10.3306"  "11.157"   "11.157"   "14.0496"  "14.0496" "14.0496" 14.0496       .
"00548A106"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "7.8512"   "11.157"   "11.157"   "11.157"   "14.876"  "14.876"   14.876 13.6364
"00788A105"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00788A113"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"053015103"             "14.876"                "17.3554"  "17.3554"  "18.1818"  "20.2479"  "21.0744"  "23.5537"  "30.9917" "43.8017" 33.4711       .
"00650F109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"052769106"             "48.7603"               "53.3058"  "52.4793"  "52.8926"  "51.2397"  "50.8264"  "50.8264"  "48.7603" "47.5207" 47.5207       .
"00738A106"             "11.157"                "11.157"   "9.9174"   "11.157"   "11.157"   "11.157"   "11.157"   "19.8347" "37.1901" 25.2066 25.2066
"007025109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"006739106"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "16.1157" "16.1157" 15.2893 15.2893
"00791N102"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00773U108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "#N/A N/A" "11.157"   "11.157"   "11.157"   "16.5289" "16.5289" 16.5289 16.5289
"00791N110"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"007624307"             "11.157"                "11.157"   "11.157"   "11.157"   "11.157"   "11.157"   "14.4628"  "15.2893" "15.2893" 15.2893       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A N/A"              "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02157M207"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L106"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L205"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00809L114"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00760J108"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"02116A104"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"007973100"             "11.157"                "11.157"   "11.157"   "11.157"   "14.876"   "14.876"   "14.876"   "17.3554" "17.3554" 18.1818 23.1405
"00808Y307"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"025537101"             "47.9245"               "54.717"   "45.6604"  "52.8302"  "51.3208"  "51.6981"  "58.8679"  "58.8679" "59.2453" 57.7358 57.3585
"#N/A Invalid Security" "#N/A Invalid Security" ""         ""         ""         ""         ""         ""         ""        ""              .       .
"#N/A Invalid Security" "#N/A Invalid Security" ""         ""         ""         ""         ""         ""         ""        ""              .       .
"00771V108"             "#N/A N/A"              "#N/A N/A" "#N/A N/A" "10.3306"  "11.157"   "14.0496"  "14.0496"  "17.3554" "17.3554" 17.3554 21.9008
"019170109"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"006743306"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"050734201"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
"04005A203"             "#N/A N/A"              ""         ""         ""         ""         ""         ""         ""        ""              .       .
end
tempfile using
save `using'

use `using', clear
drop if substr(cusip, 1, 4) == "#N/A"
destring esg_*, replace ignore("#N/A")
reshape long esg_, i(cusip) j(year)
rename esg_ esg
tempfile cleaned_using
save `cleaned_using'

use `master', clear
merge m:1 cusip year using `cleaned_using'
    Now, in your example data, it turns out there is only one cusip year combination that appears with valid data in both data sets. Presumably in your full data there will be more matches.
    • 1 like

    Comment

    • #3
      Hi Clyde, Thank you so much for your reply. It is very helpful especially the consideration of #N/A

      Comment

      Previous Next
      Working...
      X