Merging two datasets r(459) variable does not uniquely identify observations in the master data

[Josephine Nicolai]

Hi,

I'm trying to merge 2 datasets based on 2 variables. I've got panel data where I have financial and non-financial information of firms for multiple years. I'd like to merge based on CIK code and year. However, I'm constantly getting an error. I checked the excel files and both datasets should merge based on year and cik. I'm doing a one-to-one merge.
Command:
merge 1:1 CIK year using "/Users/josephine/Downloads/oefenen firmfilings.dta"

Should year and CIK variable be an integer of string variable? The variable type of 'year' is "int" and for the variable 'CIK', the type is "long" in both datasets. Also, the variable names are the same in both datasets. This means it shouldn't get an error, right?

I hope I gave enough information to get a solution, thanks in advance!

Kind regards,
Josephine

[Nick Cox]

The variable or storage type should indeed be the same for each variable but that is not a problem here and the error message is quite different.

You have duplicate observations in one or both datasets that rule out a 1:1 merge. Looking at the results of

duplicates list CIK year

in each dataset is a way forward,

[Josephine Nicolai]

Thankyou! There are a lot of duplicates indeed.

How do I drop the duplicates that have the least information of all the variables? For example:
Observation 1 has more information available than observation 2, so I would like to drop the second observation (when they are duplicates based on CIK and year), or in other words, merge duplicate observations if that makes sense?

[Nick Cox]

Merge as a word in English makes sense for what you want, but in Stata terms it is more likely to be a collapse and/or drop. What kind of collapse or drop you need is hard to tell without more details.