Error r(109) type mismatch

[Marcia Diaz]

I am trying to generate a map of Germany. After translating my shapefiles into dta files, I import my master dta file (locscode2) and later import the file which includes all the coordinates of German cities (x-ycoord). After merging both dta files, I did encounter problems before because the 'longitude' variable comes as a string variable for some reason. That is why I use the 'destring' command, in order for my spmap command to work later on. Since I am missing some coordinates in my data, I am manually inserting these in my do file. The error it gives comes up in the last command line. I'm guessing it might be related to the 'longitude' variable. Does anyone know what it might be? Thanks! Here's my code:


clear

* Translation of files (shp to dta)
shp2dta using DEU_adm1, database(deudb) coordinates(deucoord) genid(id)
shp2dta using DEU_adm3, database(deudb3) coordinates(deucoord3) genid(id3)

* Directory/Import excel file
cd "/Users/marciadiazclaudio/Desktop/shapefiles"
import excel "location(scode)2.xlsx", sheet("Hoja1") firstrow clear
save "locscode2.dta", replace

use locscode2
drop projCODE

*Import file with all coordinates for german cities
import excel "x-ycoord.xlsx", sheet("Hoja1") firstrow clear
duplicates drop location, force
save allcoord.dta,replace

use locscode2
merge m:m location using allcoord.dta
drop _merge
destring longitude, replace
generate id = _n

* Fill in missing coordinate values
replace latitude = 51.099188 if location == "Auerstedt"
replace longitude = 11.588744 if location == "Auerstedt"

[Clyde Schechter]

So, if Stata is importing the longitude variable as a string, there is almost certainly a good reason for that--it doesn't do that sort of thing capriciously. The -destring longitude, replace- command will detect that there is something non-numeric about that variable and will refuse to carry out the destring action. For reasons unknown, and quite contrary to usual Stata behavior, this does not result in a red error message and a break in execution. Rather you just get a warning like -longitude contains nonnumeric charcters; no replace- and execution continues from that point. My guess is that that is what happened. So you were unaware that longitude is still a string variable until your -replace- command choked on it.

The fix is to find out what is causing longitude to be non-numeric and then repair that before you try to -destring- it (or, if the problem is amenable to it, making use of the -ignore()- or, if there is no other way around it, the -force- option of -destring- to get the conversion to numeric.)

To find the problem cases that make longitude non-numeric you can run:

list longitude if missing(real(longitude))

Once you know those results, you can repair the variable.

Added: Sometimes when you run the code above everything looks numeric. In that case, it is typically due to embedded non-printing characters that don't show up with -list- but that prevent conversion to numeric type. To identify those, run:

charlist longitude if missing(real(longitude))
// -charlist- is written by Nick Cox and available from SSC
return list

and pay particular attention to the r(ascii) results. The digits 0-9 are represented as ascii 48 through 57. A decimal point is 46, and a negative sign is 45. Anything else will prevent conversion to numeric and must be removed (or -ignore()-d in -destring-).

All of that said, you probably have a much bigger problem than this in your code. -merge m:m- is almost always wrong and almost always produces garbage. (When I say almost always, in 22 years of using Stata, I have only once encountered a situation where -merge m:m- was actually the right thing to do.) When people are tempted to use -merge m:m- almost always one of the following is true:

1. They really need to do a 1:1, 1:m or m:1 merge, but the merge key they are using has omitted a key variable.

2. They really need to do what -joinby- or -cross- does.

3. They really need to do a 1:1, 1:m or m:1 merge, but cannot make it work because one or both data sets erroneously contains duplicate observations on the merge key that should not be there. This might be remedied by dropping purely duplicate observations, by reconciling the inconsistencies of observations that duplicate the merge key but disagree on other variables and keeping only the correct one, by aggregating the observations up to the merge key level using appropriate summary statistics, or, occasionally, by -reshape-ing to wide layout. Then again, often the presence of duplicate observations on the merge key simply indicate that an error has occurred in the process of managing the data up to this point, and the solution requires going back and fixing those errors.