Hello All,
I have a complex difficulty with merging two datasets.
The first difficulty concerns the name of the identifier, which is a company name.For example, in one dataset, the identifier is named "AT&T", and in the other dataset it is written "AT&T LLC".
The second difficulty regards the fact that there are repetitive identifiers in both datasets. For example, the identifier "AT&T" appears 100 times in one dataset and 50 times in the other dataset. The difference in many observations with the same company name is the different announcements released by the same company in one dataset and the amount and dates of penalties in the second dataset. I should try to match the penalty, followed by the announcement. I have dates for each penalty and the announcement, and I should match the penalty with the first announcement that followed the date of the penalty. For example, if in May 2020 AT&T was penalized, and it released announcements in June, July, August of 2020, I want Stata to merge with the earliest announcement - that is, June, 2020.
Below is the subtraction from the first dataset:
And the subtraction of the second dataset:
Thank you very much in advance! Any help will be greatly appreciated!
Regards,
Nick
I have a complex difficulty with merging two datasets.
The first difficulty concerns the name of the identifier, which is a company name.For example, in one dataset, the identifier is named "AT&T", and in the other dataset it is written "AT&T LLC".
The second difficulty regards the fact that there are repetitive identifiers in both datasets. For example, the identifier "AT&T" appears 100 times in one dataset and 50 times in the other dataset. The difference in many observations with the same company name is the different announcements released by the same company in one dataset and the amount and dates of penalties in the second dataset. I should try to match the penalty, followed by the announcement. I have dates for each penalty and the announcement, and I should match the penalty with the first announcement that followed the date of the penalty. For example, if in May 2020 AT&T was penalized, and it released announcements in June, July, August of 2020, I want Stata to merge with the earliest announcement - that is, June, 2020.
Below is the subtraction from the first dataset:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input int KeyDevelopmentsByDate str450 CompanyNames str456 KeyDevelopmentHeadline 15368 "OpenNetwork Technologies" "OpenNetwork Technologies Releases DirectorySmart Version 4.7" 15368 "Océ Imagistics, Inc." "Imagistics Introduces DL155/DL185 Digital Copier/Printers" 15368 "Alcatel-Lucent" "Alcatel's New Fiber-to-the-User Solution Lights Way to Profitable Broadband Services" 15368 "Neoforma, Inc." "Marketplace@Novation Customers Experience Value in Using New and Enhanced Applications" 15368 "H-Quotient, Inc." "H-Quotient Inc. Unveils New Products and Marketing Plans for 2002" 15368 "parts.com, Inc." "The 'New Parts.com' Released at the NADA in New Orleans" 15368 "Oracle America, Inc." "Sun Microsystems Increases Cross Platform Support Within Sun One" 15368 "Automatic Data Processing, Inc. (NasdaqGS:ADP)" "Experian Automotive Teams With ADP" 15368 "TranSwitch Corporation (OTCPK:TXCC.Q)" "TranSwitch Corporation Optimizes Ethernet Over SONET/SDH Transport with Hybrid Mapper Device" 15368 "SAFLINK Corp" "SAFLINK Expands Capabilities of SAFaccess Security Product" 15368 "iA Financial Corporation Inc. (TSX:IAG)" "The Industrial Alliance Group Launches a New Secure Internet Site For its Individual Annuities Clients and Representatives" 15368 "On2 Technologies Inc." "On2 Develops Real-Time Encoding Solution" 15368 "Industryview" "Industryview Launches New Broadband Video Website" 15368 "International Internet Holdings, Inc." "Date.com Introduces Operation Love-Link" 15368 "Aquiire, Inc." "e-Catalog 'Punchout' Faster, Cheaper and Easier for B2B Buyers With New Version of Vinimaya Product" 15368 "Esker, Inc." "Esker Announces VSI-FAX for Notes 3.5" 15368 "Oracle America, Inc." "Cluen Releases Slingshot" 15368 "Workshare Technology, Inc." "Workshare Technology Releases Workshare Synergy 2.2" 15368 "A.M. Best Company, Inc." "A.M. Best Co. Launches Comprehensive News Site, Enhanced Archive" 15368 "BlueTie, Inc." "BlueTie Launches Generation 2.0 Suite of Information/Communication Tools Blazing Fast and 99.99% Reliability" 15368 "Oracle America, Inc." "Saucon Changes Web Application Development Landscape With a New Open-Source Project Called Japple" 15368 "AdvanceNet Health Solutions, Inc." "AdvanceNetHealth Solutions Introduces Enterprise Management/Connectivity for ePrescribing - ePostRx" 15368 "SPM Global Services, Inc." "Synygy Delivers on Web-Based Vision for Enterprise Incentive Management -EIM- Software" 15368 "T. Rowe Price Group, Inc. (NasdaqGS:TROW)" "T. Rowe Price Group Inc. Expands European Presence With Launch in Germany" 15368 "Neoforma, Inc." "Cirilium Launches Major Additions To Voice Over IP Product Suite" 15368 "Qorvo US, Inc." "TriQuint Semiconductor Introduces an OC192 Modulator Driver With DC - 18GHz Performance in a Surface Mount Package" 15368 "Adobe Macromedia Software LLC" "Macromedia JRun Achieves Java(TM) 2 Platform, Enterprise Edition (J2EE(TM)) 1.3 Compatibility" 15368 "Eclipsys Corporation" "Eclipsys, CPM Resource Center Partner to Provide Knowledge-Based Charting Evidence-Based Documentation Solution" 15368 "Angiotech BioMaterials Corporation" "Cohesion Technologies Launches CoSeal(R) -The First Completely Synthetic Vascular Sealing Agent" 15368 "AutoWeb, Inc. (NasdaqCM:AUTO)" "Autobytel Inc. Launches AIC's AutoSuite 2002" 15368 "Oracle America, Inc." "Quest Software is First to Deliver Complete Oracle Database Management Solution With Single Integrated Product" 15368 "NuSphere Corporation" "NuSphere Unveils Linux-Based Version of Award-Winning NuSphere PHPEd Product" 15368 "National Semiconductor Corporation" "National Semiconductor Continues to Drive High-Speed Analog Market With Next Wave of LMH Amplifiers Based on VIP10 Process" 15368 "Oracle America, Inc." "Group Software Advances Enterprise-wide Protection Against Industrial Espionage and Sexual Harassment with New securiQ Suite" 15368 "Oracle America, Inc." "Sybari Software Expands Antigen 6.0 Platform Support for Lotus Domino and Releases New Content Management Enhancements" 15368 "National Semiconductor Corporation" "National Semiconductor Drives Broadband Access Market With New Utopia Bus SerDes Controller" 15368 "Oracle America, Inc." "Stampede Technologies Announces General Availability Of TurboGold For The IBM eServer zSeries" 15368 "National Semiconductor Corporation" "National Semiconductor Introduces Three New Boundary Scan LVDS Products" 15368 "PPG Architectural Finishes, Inc." "Pittsburgh Paints Introduces Premium, High-Performance Paint With Zero VOC" 15368 "St. Jude Medical, Inc." "St. Jude Medical Announces the First Implant and U.S. Market Release of the Atlas DR/VR Implantable Cardioverter Defibrillators" 15368 "Datalogic ADC, Inc." "PSC Launches New Magellan 8500 Scanner" 15368 "LifePoint, Inc. (OTCPK:LFPI)" "LifePoint Inc. Announces Launch Event for Impact Test System" 15368 "The Walt Disney Company (NYSE:DIS)" "Disney Interactive Launches 'Plaid Banana Entertainment'" 15368 "Netaphor Software, Inc." "Netaphor Announces Beta Release of PDAlert for Wireless Monitoring of Network Devices Using PDAs" 15368 "Unicast Communications Corp." "Unicast Unveils SUPERSTITIAL(R) 300" 15368 "DataVision" "Data-Vision Opens Virtual Mortgage Folder" 15368 "eCornell" "Ivy League Online for HR Professionals: eCornell Launches HR Training Course 'Fundamentals of Employee Benefits'" 15368 "System Management ARTS, Inc." "SMARTS Broadens Reach Of Service Assurance Manager With Industry's First Smart Adapters" 15368 "Sorin CRM SAS" "ELA Medical Launches Pacing System in Europe to Target Atrial Fibrillation" 15368 "SHOP.COM, Inc." "Altura International and CyberDrawer Launch Catalog King" 15368 "ArcSight, Inc." "ArcSight Introduces First Enterprise-Class Security Management Solution" 15368 "Neoforma, Inc." "Imperito Launches Product Tailored to Needs of Small and Medium Businesses" 15368 "TeraLogic, Inc." "TeraLogic Introduces Linux-Based Cougar-L DTV Reference Platform" 15368 "Avanquest Publishing USA, Inc." "Elibrium Launches New Release of the No. 1 Selling Invoice And Estimate Software" 15368 "WebMD Health Services Group, Inc." "Priority Health Launches Comprehensive Web-Based Initiative Based on WellMed's Award Winning Health Communication Platform" 15368 "Anritsu A/S" "NetTest Delivers Industry's First SIGTRAN Testing Solutions" 15368 "CustomWeather, Inc." "CustomWeather Launches MyForecast.com - New Web Site Provides Consumers Extensive" 15368 "Taleo Corp." "Recruitsoft introduces a new paradigm in staffing optimization" 15368 "Medivance, Inc." "Medivance Inc.Begins Market Introduction Of The Arctic Sun, The Next Generation Of Patient Temperature Management Systems" 15368 "Intuit Canada ULC" "Intuit Canada's Online Solution Responds to Canadians' Endorsement of the Web" 15368 "Sword CTSpace, Inc." "Citadon Introduces Next Generation Product: Citadon CW" 15368 "Information Systems Group, Inc.; Toshiba of Canada Limited" "Toshiba's newest Satellite series notebooks feature first Canadian mobile computers with Intel(R) Pentium(R) 4 processor and luxury multimedia hardware" 15368 "Gartner, Inc. (NYSE:IT); TeraQuest Metrics Inc." "Gartner and TeraQuest Announce Groundbreaking Approach for Projecting ROI on Application Development Improvement Investments" 15368 "Intellor Group Inc." "Intellor Group Launches Web Services Reality Spring 2002 Conference" 15368 "Oracle America, Inc." "BellSouth Introduces Enterprise Data Backup Service" 15368 "Palm, Inc." "Palm Inc. launched the Palm(TM) i705 handheld" 15368 "Verisity Ltd." "Verisity's Specman Elite Supports C-Based System Design" 15368 "Palm, Inc." "Palm Announces Enterprise-Focused Messaging Solution" 15368 "Accenture plc (NYSE:ACN)" "Accenture Introduces Mobile Service Bureau To Help Companies Achieve Wireless Connectivity To Enterprise Data" 15368 "Verizon Communications Inc. (NYSE:VZ)" "Verizon Wireless Launches Nation's First Major Advanced Wireless Network" 15368 "Aspen Technology, Inc. (NasdaqGS:AZPN)" "AspenTech Transforms Business and Engineering Decision-Making for the Process Industries" 15368 "Ingram Micro Inc." "Ingram Micro Expands Enterprise Offerings With New Product Solutions" 15368 "Borland Software Corporation" "Borland Unveils C++ Application Development Strategy for 2002" 15368 "Masimo Corporation (NasdaqGS:MASI)" "Masimo Introduces Three New Products Of The Society Of Critical Care Medicine" 15368 "Virtual Universe Corp." "Virtual Universe Launches New Website and Three New Internet Audio Conferencing Products" 15368 "HelloSoft, Inc." "HelloSoft Introduces New High-Performance Voice and Wireless Communications Solutions for Texas Instruments DSPs" 15368 "Zope Corporation" "Zope Corporation Releases Zope 2.5 and Python 2.2" 15368 "Neoforma, Inc." "New Premio Proteus PC Features Reduced Form Factor Design and Convertible Chassis with Intel Pentium 4 Performance" 15368 "NIKSUN Incorporated" "NIKSUN's NetX Suite Furnishes Enterprise-Wide View of Network Performance for Maximum Network Monitoring and Management" 15368 "Aprima Medical Software, Inc." "iMedica Introduces PhysicianSuite for Medical Enterprises" 15368 "Full Time Solutions Inc" "Full Time Solutions Announces Launch of Web-Based PEO Service Portal" 15368 "Neoforma, Inc." "Comverse Introduces Its CAMEL Phase 2 IN-Based Prepaid Solution" 15368 "IMS Health Holdings, Inc." "FIP and IMS HEALTH Launch Web Information Resource to Support Pharmacists Around the World" 15368 "LeCroy Protocol Solutions Group" "CATC Announces New IBTracer Software" 15368 "Learning Tree International, Inc. (OTCPK:LTRE)" "ASP.NET and WebForms is Subject of New Hands-on Course Released by Learning Tree" 15368 "The First Years Inc." "The First Years(R) Unveils First Semi-Disposable Spill-Proof Cups Toddlers Can Use And Lose --With No Worry" 15368 "Great-West Retirement Services, Inc." "EducatorsMoney(SM) Launches 403(b)/457 Online Retirement Service For Education Marketplace" 15368 "Sussex Systems Inc" "Sussex Systems Releases Advanced Examples Version Q1-2002 Featuring Domino Everyplace Mobile Application" 15368 "The Cobalt Group, Inc." "Chrysler Group Provides Dealers With State-of-the-Art, Online Business Tools" 15368 "International Business Machines Corporation (NYSE:IBM)" "Lotus Software From International Business Machines Corp. To Provide Convenience And Flexibility For Customers With New Deployment And Services Options For Lotus Sametime" end format %td KeyDevelopmentsByDate
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str61 parentcompany str15 penaltyamount int penaltyyear long penaltydate "ABC Supply" "$6,635" 2007 20071001 "United Natural Foods" "$51,000" 2001 20010501 "United Natural Foods" "$22,260" 2007 20070703 "US Foods Holding" "$21,200" 2001 20010322 "Beacon Roofing Supply" "$202,463" 2010 20100701 "Beacon Roofing Supply" "$8,500" 2003 20030117 "Beacon Roofing Supply" "$8,821" 2000 20000711 "United Natural Foods" "$19,600" 2005 20050610 "AmerisourceBergen" "$20,000" 2003 20030206 "AmerisourceBergen" "$7,000" 2006 20060913 "AmerisourceBergen" "$25,000" 2008 20080430 "AmerisourceBergen" "$20,000" 2008 20080925 "Andersons Inc." "$5,300" 2015 20150109 "Andersons Inc." "$13,750" 2007 20070417 "Andersons Inc." "$8,100" 2016 20160108 "Andersons Inc." "$7,700" 2011 20110110 "Andersons Inc." "$7,100" 2010 20101109 "WESCO International" "$5,526" 2004 20040511 "WESCO International" "$7,848" 2011 20110305 "Arrow Electronics" "$20,000" 2006 20060614 "Associated Wholesale Grocers" "$3,742,800" 2000 20000608 "Avnet" "$27,684" 2014 20141222 "Univar Solutions" "$10,000" 2010 20100324 "Univar Solutions" "$10,000" 2005 20050914 "Univar Solutions" "$8,000" 2012 20120305 "Global Partners LP" "$30,000" 2013 20130603 "Global Partners LP" "$25,000" 2012 20120824 "Global Partners LP" "$16,500" 2015 20151123 "Global Partners LP" "$8,000" 2015 20150126 "Global Partners LP" "$5,000" 2015 20151123 "Bozzuto's" "$12,000" 2000 20000131 "Bozzuto's" "$17,245" 2014 20140315 "ABC Supply" "$30,283" 2005 20050701 "Brenntag" "$5,000" 2004 20040121 "Brenntag" "$5,000" 2014 20140916 "Brenntag" "$6,221" 2010 20100810 "Brenntag" "$5,000" 2004 20040123 "Brenntag" "$30,381" 2010 20100810 "Brenntag" "$5,000" 2016 20160509 "Brenntag" "$28,739" 2010 20100810 "Brenntag" "$7,000" 2014 20140325 "Builders FirstSource" "$12,717" 2007 20070920 "Builders FirstSource" "$9,903" 2004 20041001 "Builders FirstSource" "$5,950" 2004 20040830 "C&S Wholesale Grocers" "$779,902" 2007 20071226 "C&S Wholesale Grocers" "$55,320" 2008 20080803 "C&S Wholesale Grocers" "$14,481" 2015 20150801 "C&S Wholesale Grocers" "$10,038" 2005 20050510 "C&S Wholesale Grocers" "$12,573" 2007 20070321 "C&S Wholesale Grocers" "$126,700" 2012 20120516 "C&S Wholesale Grocers" "$85,000" 2015 20151216 "C&S Wholesale Grocers" "$49,063" 2013 20130831 "Cardinal Health" "$34,000,000" 2016 20161227 "Cardinal Health" "$26,800,000" 2015 20150420 "Cardinal Health" "$8,000,000" 2011 20110421 "Cardinal Health" "$35,000,000" 2007 20070726 "Breakthru Beverage Group" "$42,495" 2007 20070330 "Chemsolv" "$16,570" 2010 20100802 "Chemsolv" "$12,130" 2007 20071108 "Chemsolv" "$1,500,000" 2015 20151222 "Chemsolv" "$243,967" 2011 20110831 "Brenntag" "$12,500" 2001 20010227 "Brenntag" "$19,000" 2007 20071001 "Brenntag" "$6,000" 2001 20011205 "Brenntag" "$6,000" 2001 20011204 "Brenntag" "$5,000" 2007 20071001 "Brenntag" "$9,539" 2014 20140224 "Coastal Lumber" "$10,000" 2002 20020723 "Arrow Electronics" "$2,800,000" 2013 20130424 "Synnex" "$7,500" 2002 20020313 "Synnex" "$65,976" 2005 20050831 "Synnex" "$48,343" 2011 20110109 "Synnex" "$5,313" 2001 20011025 "Synnex" "$352,376" 2006 20060914 "Synnex" "$47,685" 2008 20080428 "Synnex" "$15,000" 2012 20120226 "Synnex" "$200,735" 2009 20090731 "Synnex" "$1,277,405" 2010 20100814 "DXP Enterprises" "$120,000" 2012 20120206 "Eby-Brown" "$290,000" 2008 20081014 "Eby-Brown" "$42,807" 2010 20100223 "Eby-Brown" "$19,924" 2008 20081202 "EMCO Chemical Distributors" "$7,200" 2009 20090401 "Sysco" "$4,200,000" 2013 20131119 "Sysco" "$12,956" 2012 20120912 "Geason Enterprises" "$560,000" 2015 20150407 "Watsco" "$13,530" 2015 20150326 "Genuine Parts" "$240,000" 2003 20031201 "Genuine Parts" "$15,568" 2004 20041212 "Genuine Parts" "$8,550" 2013 20130304 "Genuine Parts" "$8,070" 2014 20140109 "Southern Glazer's Wine & Spirits" "$9,960" 2008 20081222 "Southern Glazer's Wine & Spirits" "$225,000" 2008 20080718 "Global Partners LP" "$8,000" 2016 20160530 "Global Partners LP" "$8,000" 2014 20140325 "Global Partners LP" "$6,500" 2015 20150325 "Systemax" "$8,338" 2004 20040222 "Systemax" "$5,000" 2002 20020724 "Golden State Foods" "$78,842" 2004 20040607 "Graybar Electric" "$61,000" 2000 20001005 end
Thank you very much in advance! Any help will be greatly appreciated!
Regards,
Nick
Comment