having problems with matching in merge on string

[Tom Dietz]

I am trying to merge two data sets but running into a problem. Here's what I get

. merge 1:1 cid3 year using "C:\Users\Thomas Dietz\Desktop\Current work\Macro\xnational\Fraser\Fraserfive.dta"
variables cid3 year do not uniquely identify observations in the using data
r(459);

cid3 is a str12 in the open data set and a str3 in the using data set. Is that the problem? I tried
recast str3 cid, force
but that didn't seem to help.


Before this merge I merged a number of other data sets using the same syntax but in those cid3 is all str12.

As always, grateful for any help.

[Clyde Schechter]

No, that's not the issue. The error message is self-explanatory: in the using data set, Fraserfive.dta, Stata found some situations where the same values of cid3 and year appear in more than one observation. That is not compatible with a 1:1 -merge-. So you need to investigate Fraserfive.dta. You can find the offending observations with:

user Fraserfive, clear
duplicates tag cid3 year, gen(flag)
browse if flag

Next you need to figure out why they are there. Maybe this is all correct data and they are all supposed to be there. In that case, you need to go to -merge 1:m-, not -merge 1:1-. I suspect it is more likely that they should not all be there; there are errors in the data. In that case you need to figure out, for each group of observations with the same values of cid3 and year, which one, if any, is the correct one, and then eliminate the others. You should also recheck all the data management that created this data set, because the presence of such errors usually indicates errors in the code that created the data sets. Where one error lies, others may lurk nearby. So use this opportunity to thoroughly revisit the creation of this data set and eliminate any errors you can find. Better to find and fix them now than to have them trip you up later when they lead to erroneous analysis results.

[Tom Dietz]

Thanks--I was going to do the test proofing after creating the merged set but I discovered that from some odd reason there were blanks on cid3 in the using data set for a few cases. Dropping those seemed to resolve the problem. Now the laborious task of checking a data set with ~14,000 obs and about a dozen vars so far. But at least with your help I detected the immediate problem.