Variable not found in merging but does exist in both datasets

[Kelsey OHollaren]

Hi Statalist,

I'm experiencing a peculiar issue that I've never run into before. I'm performing a 1:1 merge using two datasets with the merge variables being state fips code, county fips code, census tract number, and year.



my exact code is:

merge 1:1 state_fips county tract Year using "\\vincent\vincent_b\kelseyo\Data\Gentrification_Index\Tract_Components.dta"

However, this yields tan error that the variable Year is not found. [r(111)]. I confirmed that Year is, indeed, in both the master (which I'm assuming this error is referring to) and the using datasets. In fact, I tabulated observations by Year in both datasets just to make sure.

I'm curious if anyone else has run into this issue and may have some ideas on troubleshooting. I'm happy to provide more details, but I think this is enough info to get started.

Many thanks,
Kelsey

[Clyde Schechter]

I have never run into a problem like this in my 30 years of pretty-much daily use of Stata. In fact, in my experience, -merge- is one of the most reliable, bug-free commands in all of Stata. Whenever I have thought -merge- was mistaken, a little investigation would turn up that, in fact, I was mistaken. So I expect we will find that your description of what is happening overlooks some important detail.

However, you give us nothing to go on other than the -merge- command itself. To see what is causing your problem, we need to see example data from both data sets.* Also, you stated that you tabulated observations by Year in both datasets--but you didn't show the results of those tabulations. So please post back with that information.

If I were to stick my neck out and speculate on the problem, I think the most likely thing is that one of the variables you think is Year is actually named something else. Perhaps one of them is year. That matters, as Stata is case sensitive about pretty much everything. Or maybe one of them is misspelled, with say inverted order of the two vowels in the middle--those things can trick the human eye easily. Or perhaps one of the variable names is contaminated with a non-printing character--which your eyes cannot see, but Stata does. In any case, it is overwhelmingly likely that the data are simply not what you perceive them to be, and therein lies the problem and root of the solution.

But unless I have guessed right, we really need to see the data to know.

*The only way to show example data that is guaranteed to contain all of the needed information, including metadata, to faithfully replicate your situation, is to use the -dataex- command. If you are running version 18, 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.