example for merge m:m

[River Huang]

Dear All, It is advised in the forum that, whenever possible, do not use

merge m:m ...

I wonder if someone can offer an example that, by doing this, it will provide incorrect results? Thanks.

[Joseph Coveney]

I think that it's more like the counter example: can you provide an example where it would provide the correct results?

That is, what is an m:m merge supposed to look like?

[Andrew Musau]

Robert Picard shows this in #30 of this thread following a failed attempt by one of our regular contributors.

[River Huang]

Dear Joseph, Thanks for the reply.

[River Huang]

Dear Andrew, Thanks for the example. I will have a look.

[William Lisowski]

It is advised in the forum that, whenever possible, do not use merge m:m ...

That advice is not limited to posts in this forum. The following is taken directly from the Stata Data Management Reference Manual PDF. Even the source of the code disavows it.

m:m merges

m:m specifies a many-to-many merge and is a bad idea. In an m:m merge, observations are matched within equal values of the key variable(s), with the first observation being matched to the first; the second, to the second; and so on. If the master and using have an unequal number of observations within the group, then the last observation of the shorter group is used repeatedly to match with subsequent observations of the longer group. Thus m:m merges are dependent on the current sort order—something which should never happen.

Because m:m merges are such a bad idea, we are not going to show you an example. If you think that you need an m:m merge, then you probably need to work with your data so that you can use a 1:m or m:1 merge. Tips for this are given in Troubleshooting m:m merges below.

[Joseph Coveney]

Maybe fodder for the Stata 16 wishlist: merge m:m . . . throws an exception. And old behavior not returned under a version prefix.

[River Huang]

@William Lisowski and @Joseph Coveney, Thanks again.