How to merge M&A data to 13f data?

[Wenyu Yang]

Hi all,

I am new to STATA. I have difficulty in doing M&A analysis when I try to merge M&A data from Thomson-Reuters with data from the 13f dataset. I want to add announcement dates from Thomson-Reuters to the 13f dataset to calculate institutional ownership concentration. But there are many same acusip6 (6-dig cusips of acquirors) with different announcement dates in dataset 1, and different mgrno (i.e. manager numbers) with various rdate (i.e. report date) in dataset 2. I want to get a merged dataset that each announcement date is matched with all mgrno that have the same acusip6. Both datasets have the same but duplicate acusip6. Could anyone tell me how to do that?

Part of these two datasets are as follows:
Dataset1

ann_date	deal_value	acusip6
14-Dec-09	118.09	002824
10-Sep-09	410	002824
28-Sep-09	7603.45	002824
12-Jan-09	1377.735	002824
14-Oct-09	6.819	004848
05-Jun-09	207.52	00508Y

Dataset2

mgrno	acusip6	rdate	shrheld	shrout
185	002824	30-Jun-09	1585993	1545459
185	002824	31-Mar-09	1606049	1545383
185	002824	30-Sep-09	1540941	1545912
185	002824	31-Dec-09	1543268	1546738
195	002824	31-Dec-09	328758	1546738
195	002824	30-Jun-09	518482	1545459
195	002824	30-Sep-09	2300159	1545912
195	002824	31-Mar-09	497639	1545383
205	002824	30-Jun-09	66602	1545459
205	002824	31-Mar-09	67229	1545383
205	002824	31-Dec-09	68792	1546738
205	002824	30-Sep-09	66977	1545912
220	002824	31-Mar-09	688465	1545383
220	002824	30-Jun-09	146925	1545459
220	002824	30-Sep-09	287175	1545912
220	002824	31-Dec-09	988025	1546738
260	002824	30-Jun-09	320000	1545459
260	002824	31-Dec-09	320000	1546738
260	002824	31-Mar-09	320000	1545383
260	002824	30-Sep-09	320000	1545912
350	002824	30-Jun-09	17139	1545459
350	002824	31-Mar-09	25384	1545383
350	002824	30-Sep-09	13627	1545912
350	002824	31-Dec-09	13827	1546738
482	002824	30-Sep-09	165905	1545912
482	002824	31-Dec-09	233310	1546738
482	002824	30-Jun-09	235878	1545459
482	002824	31-Mar-09	275037	1545383
650	002824	31-Mar-09	2677987	1545383
650	002824	30-Sep-09	364531	1545912
650	002824	30-Jun-09	693589	1545459
650	002824	31-Dec-09	338134	1546738
800	002824	30-Jun-09	125163	1545459
800	002824	31-Mar-09	123773	1545383
885	002824	31-Mar-09	2616963	1545383
885	002824	30-Jun-09	2868436	1545459
885	002824	31-Dec-09	1764664	1546738
885	002824	30-Sep-09	1834466	1545912
1275	002824	30-Sep-09	37474	1545912
1275	002824	31-Mar-09	45308	1545383
1275	002824	31-Dec-09	36761	1546738
1275	002824	30-Jun-09	37389	1545459
1285	002824	30-Sep-09	281509	1545912
1285	002824	30-Jun-09	284016	1545459
1285	002824	31-Dec-08	278202	1545383
1285	002824	31-Dec-09	278049	1546738
180	004848	30-Sep-09	843620	11179
180	004848	30-Jun-09	868720	11282
180	004848	31-Dec-09	679860	11647
180	004848	31-Mar-09	868720	11468
5300	004848	30-Jun-09	39500	11282
5300	004848	30-Sep-09	39500	11179
5300	004848	31-Mar-09	39500	11468
5300	004848	31-Dec-09	39500	11647
5720	004848	31-Mar-09	1000	11468
5720	004848	30-Jun-09	1000	11282
7815	004848	31-Dec-09	13400	11647
7900	004848	30-Jun-09	23525	11282
7900	004848	30-Sep-09	21209	11647
7900	004848	30-Sep-09	21209	11179
220	00508Y	31-Mar-09	200	41201
650	00508Y	31-Dec-09	21600	43267
650	00508Y	30-Sep-09	22100	43086
650	00508Y	31-Mar-09	25000	41201
650	00508Y	30-Jun-09	21600	40913
1365	00508Y	31-Dec-09	9620	43267
2470	00508Y	30-Jun-09	60698	40913
2470	00508Y	31-Dec-09	64567	43267
2470	00508Y	30-Sep-09	77429	43086
2470	00508Y	31-Mar-09	39089	41201
4714	00508Y	31-Dec-09	16300	43267
4719	00508Y	30-Jun-09	5218799	40913
4719	00508Y	31-Dec-09	5452799	43267
4719	00508Y	30-Sep-09	5443499	43086
4719	00508Y	31-Mar-09	4108799	41201
5720	00508Y	30-Jun-09	4877	40913
5720	00508Y	30-Sep-09	4677	43086
5720	00508Y	31-Mar-09	8477	41201
5720	00508Y	31-Dec-09	4977	43267
6093	00508Y	31-Dec-09	6321	43267
6093	00508Y	30-Jun-09	20166	40913
6093	00508Y	30-Sep-09	23503	43086
6098	00508Y	30-Sep-09	1421604	43086
6098	00508Y	30-Jun-09	423796	40913
6132	00508Y	30-Jun-09	258934	40913
6132	00508Y	30-Sep-09	119223	43086
6132	00508Y	31-Dec-09	47328	43267
6132	00508Y	31-Mar-09	245400	41201
6155	00508Y	31-Mar-09	12386	41201

Thanks a lot,
Wenyu

[Clyde Schechter]

I want to get a merged dataset that each announcement date is matched with all mgrno that have the same acusip6.

That is exactly what the -joinby- command does. You cannot get this from -merge-

use dataset1, clear
joinby acusip6 using dataset2

Read -help joinby- for more details on how the command works. In particular, there may be unmatched observations in either data set, and you can specify which, if any, you wish to keep.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 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.

[Wenyu Yang]

Thank you Clyde. That worked great. I will post my requests with examples by using -dataex- next time.