Many to Many merge

[Rik Velthoven]

Dear Statafriends,

I currently have two datesets I want to combine. In the first dataset I have data regarding the merge of two firms from different countries and their CAR. I have to match this dataset with my second dataset which contains the CAR of two different firms which are merged in the same country. I want to compare the CAR of the firms matched in the same country and industry compared to firms match in a different country but the same industry. I will do the merge in two steps. First I will link the datasets on the country of the target of one of the firms from my first dataset. Later I will do the same for the country of the acquiror. My problem is however that in my first dataset I have multiple datapoints I have to link on, cause I perform a link on the following variables; TargetNation TargetIndustry & AcquirorIndustry. So in many cases I facer the problem that I have multiple observation in the master and using dataset, while sametimes a merge between two countries in the same industry occure multiple times in both datasets. My question is, if I use a many;many merge whether this could provide a sufficient link.

I thank you on beforehand,
Rik

[Clyde Schechter]

The use of m:m merges almost invariably produces useless garbage. There are very rare situations (I have encountered only one in 24 years of using Stata) where the results of a m:m merge could be appropriate, but I am quite confident this isn't one of them.

I find your post, however, very confusing. There is much too much going on here to try describing it in words. Please post examples of both data sets, and then create a hand-worked version of the results you would hope to see when they are combined the way you wish, and post that. Be sure to use the -dataex- command to post all three of these. Whatever the solution is, coming up with it will probably require that somebody experiment with example data; at the very least this is not the kind of thing that anybody would confidently post code for without at least testing it out on a relevant example. So example data is really critical here. And -dataex- is the best way to give example data.

If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

Finally, although it isn't crucial to answering your question in this case, please bear in mind that this is an international multi-disciplinary forum. It is best to avoid using unexplained abbreviations like CAR that are disciplinary jargon. Only use abbrevations that every university-educated adult, regardless of discipline, would immediately recognize and understand, please, or, exlpain them on first use.

[Rik Velthoven]

Dear Shechter,
Thank you for your explanation on how to post a proper post. Underneath I have produced the two datasets I want to merge together. The first datasets contains mergers between two companies which are located in different countries. I have calculated the return of these companies surrounding the announcement date. I am testing for cultural differences between countries, and to compare the return of the two companies merged which are located in different countries, I want to link companies which merged in the same country and the same industry. I want to do this to compare the returns of mergers between companies in a certain industry but from different countries to the return from companies in the same industry (as the merger from my first dataset) but also from the same country . So in the first dataset I have the following variables; TargetNation (which is the nation in which the target company is located), TargetPrimarySICCode (which is the industry code in which the target company is listed for example the Finance industry), AcquirorPrimarySICCode (which is the industry code in which the acquiror company is listed)

Dataset1:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 TargetName str14 TargetNation str2(TargetPrimarySICCode AcquirorPrimarySICCode)
"Learning Care Group Inc"      "United States"  "83" "83"
"Entrelec Group"               "France"         "36" "36"
"Power-One Inc"                "United States"  "36" "36"
"Baldor Electric Co"           "United States"  "36" "36"
"Thomas & Betts Corp"          "United States"  "36" "36"
"Teledata Communications Ltd"  "Israel"         "36" "36"
"Alert Centre Inc"             "United States"  "73" "73"
"Britannia Security Group PLC" "United Kingdom" "36" "73"
"Automated Security(Hldgs)PLC" "United Kingdom" "17" "73"
"Prime Commercial Bank Ltd"    "Pakistan"       "60" "60"
end

Dataset2:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 TargetName1 str14 TargetNation str2(TargetPrimarySICCode AcquirorPrimarySICCode)
"Buckeye Financial Corp"         "United States" "36" "36"
"Mayflower Financial Corp"       "United States" "60" "60"
"Energy Development Partners"    "United States" "13" "13"
"Easco Hand Tools Inc"           "United States" "34" "38"
"ShareBase Corp"                 "United States" "35" "35"
"Sellersville S&L Assn,Perkasie" "United States" "36" "36"
"NEECO Inc"                      "United States" "50" "17"
"Universal Voltronics Corp"      "United States" "36" "35"
"Systematics Inc"                "United States" "73" "48"
"MGM/UA Communications Co"       "United States" "78" "78"
end

My final dataset have to look like this given the two previous example dataset:

"Power-One Inc" "United States" "36" "36" "Buckeye Financial Corp"
"Power-One Inc" "United States" "36" "36" "Sellersville S&L Assn,Perkasie"
"Baldor Electric Co" "United States" "36" "36" "Buckeye Financial Corp"
"Baldor Electric Co" "United States" "36" "36" "Sellersville S&L Assn,Perkasie"
"Thomas & Betts Corp" "United States" "36" "36" "Buckeye Financial Corp"
"Thomas & Betts Corp" "United States" "36" "36" "Sellersville S&L Assn,Perkasie"

I hope this post will clear things up. I thank you on beforehand for your help. If anything is not clear I can explain a little bit more, sorry for my broken english it's not my mother's tongue

Regards,
Rik

[Clyde Schechter]

Thank you for the data examples and clarifications. This is not a merge, it's a join, and the -joinby- command does it:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 TargetName str14 TargetNation str2(TargetPrimarySICCode AcquirorPrimarySICCode)
"Learning Care Group Inc"      "United States"  "83" "83"
"Entrelec Group"               "France"         "36" "36"
"Power-One Inc"                "United States"  "36" "36"
"Baldor Electric Co"           "United States"  "36" "36"
"Thomas & Betts Corp"          "United States"  "36" "36"
"Teledata Communications Ltd"  "Israel"         "36" "36"
"Alert Centre Inc"             "United States"  "73" "73"
"Britannia Security Group PLC" "United Kingdom" "36" "73"
"Automated Security(Hldgs)PLC" "United Kingdom" "17" "73"
"Prime Commercial Bank Ltd"    "Pakistan"       "60" "60"
end
tempfile one
save `one'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 TargetName1 str14 TargetNation str2(TargetPrimarySICCode AcquirorPrimarySICCode)
"Buckeye Financial Corp"         "United States" "36" "36"
"Mayflower Financial Corp"       "United States" "60" "60"
"Energy Development Partners"    "United States" "13" "13"
"Easco Hand Tools Inc"           "United States" "34" "38"
"ShareBase Corp"                 "United States" "35" "35"
"Sellersville S&L Assn,Perkasie" "United States" "36" "36"
"NEECO Inc"                      "United States" "50" "17"
"Universal Voltronics Corp"      "United States" "36" "35"
"Systematics Inc"                "United States" "73" "48"
"MGM/UA Communications Co"       "United States" "78" "78"
end

joinby TargetNation TargetPrimarySICCode AcquirorPrimarySICCode using `one', unmatched(none)