Merge issue

[Muhammad Ramzan]

Hi


I am trying to merge two datasets using the following command

merge 1:1 lon lat mdate using "pm2p5.dta"


Both dataset has the same number of observations, and the lat long mdate values are the same in both datasets. Even then none of the observations is matched.




Result Number o
> f obs
------------------------------------
> -----
Not matched
> 1,296
from master
> 648 (_merge==1)
from using
> 648 (_merge==2)

Matched
> 0 (_merge==3)
------------------------------------
> -----

[Inaamul Haq]

Looks like lat lon mdate do not identify matches in the two datasets. Make sure you have varname or varlist which identifies the matches.

[Muhammad Ramzan]

Thanks

I have check it manually in Excel and the lat lon mdate does match 1:1, excel file is attached please

[Inaamul Haq]

use pm2p5
sort mdate lat lon
save, replace
use cv, clear
sort mdate lat lon
merge 1:1 _n using pm2p5

[Muhammad Ramzan]

Thanks, it worked, but why the following code is not working

use pm2p5 ,clear 
sort mdate lat lon
save, replace
use cv, clear
sort mdate lat lon
merge 1:1 mdate lat lon using pm2p5

[Muhammad Ramzan]

why this command
merge 1:1 mdate lat lon using pm2p5 is not working as all the values for the mdate lat lon are same

[William Lisowski]

Your problem is one of numeric precision. You are merging using Stata double variables, but they were created in different manners and while they match when displayed with 8 places to the right of the decimal point, if they are shown with more places the differences become apparent.

 use "~/Downloads/2filesv4/cv", clear

. list lat lon if date=="2003-01-01", clean

               lat           lon  
  1.   33.48292969   74.11377828  
  2.   33.51330265   73.90576445  
  3.   33.51459100   73.75728486  

. format %20.14f lat lon

. list lat lon if date=="2003-01-01", clean

                     lat                 lon  
  1.   33.48292969000000   74.11377828000001  
  2.   33.51330265000000   73.90576445000001  
  3.   33.51459100000000   73.75728486000000  

.
. use "~/Downloads/2filesv4/pm2p5", clear

. list lat lon if mdate==tm(2003m1), clean

               lat           lon  
  1.   33.48292969   74.11377828  
  2.   33.51330265   73.90576445  
  3.   33.51459100   73.75728486  

. format %20.14f lat lon

. list lat lon if mdate==tm(2003m1), clean

                     lat                 lon  
  1.   33.48292968999995   74.11377827999976  
  2.   33.51330264999985   73.90576445000022  
  3.   33.51459100000008   73.75728485999969  

.

[Muhammad Ramzan]

Thanks William Lisowski

I have created these using the code you provided in the other post. I have applied the code to the two different datasets. What is the solution please to solve this issue

Thanks

[Clyde Schechter]

use pm2p5 ,clear
recast float lat lon, force
sort mdate lat lon
save, replace
use cv, clear
recast float lat lon, force
sort mdate lat lon
merge 1:1 mdate lat lon using pm2p5

[William Lisowski]

In post #20 of your earlier topic at

https://www.statalist.org/forums/for...12#post1693712

I discussed the excessive precision implied by the length of the latitudes and longitudes you wished to include in your data.

I would recommend the following additions to the code presented by Clyde Schechter in post #9

use pm2p5 ,clear
recast float lat lon, force
sort mdate lat lon
duplicates report mdate lat lon
save, replace
use cv, clear
recast float lat lon, force
sort mdate lat lon
duplicates report mdate lat lon
merge 1:1 mdate lat lon using pm2p5

because in shortening the latitudes and longitudes currently precise to about a millimeter, you may have multiple distinct values shortening to the same shorter value.

[Clyde Schechter]

Yes, William Lisowski raises an excellent point that I neglected to consider.