Trouble Merging Datasets on Postal Code from Same Raw Source

[Michael Duarte Goncalves]

Hello everyone,

I’m working on merging two datasets from the same source in Stata but encountering issues with the merge process. Here are the details:

1. Objective: I want to merge a cleaned dataset (all_cars_cleaned_cardesc15-19_fordemand.dta) with another dataset (DGT_data_with_postal_code.dta) primarily on the postal code.
2. Datasets:
   • Cleaned Dataset: Contains variables like brand, model_dup_original, fuel, engine_cap, etc.
   • Other Dataset: Includes postal codes and other vehicle details.
3. Steps Taken:
   • Cleaned and prepared both datasets.
   • Renamed variables for consistency.
   • Dropped duplicates using duplicates drop, to ensure to have my "1" part for subsequent merge
   • Used merge 1:n to combine datasets.
4. Issue: The merge results are not as expected. There seem to be discrepancies or unmatched records.

Question: How can I improve the merge between these two datasets? Are there specific steps or checks I should perform to ensure a perfect match on postal codes and other key variables from my using dataset?

Here’s a snippet of my Stata data set that I obtain from my merge 1:n. As we can see, both rendered outputs are the same, but still not merged between them:

* Example generated by -dataex-. For more info, type help dataex
clear
input float fec_tramitacion_date str23 brand str2 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str17 emission str4 ev_type str26 model_dup_original long postal_code byte merge_or_not
. "FIAT" "1" 1956              13.14 2100 17015 103 144 "EURO 6W" "" "FIAT 500X"     . 1
. ""     "1" 1956 13.140000343322754 2100 17015 103 144 ""        "" "FIAT 500X" 17820 2
end
format %td fec_tramitacion_date
label values merge_or_not _merge
label def _merge 1 "Master only (1)", modify
label def _merge 2 "Using only (2)", modify

I should have the 4,000,414 observations from my master data, but that is not the case

    Result                      Number of obs
    -----------------------------------------
    Not matched                    15,331,702
        from master                 3,914,756  (merge_or_not==1)
        from using                 11,416,946  (merge_or_not==2)

    Matched                            63,001  (merge_or_not==3)
    -----------------------------------------

Here is a sample of my master dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input float fec_matricula_date str29 brand str26 model_dup_original str2 fuel long engine_cap float fiscal_power long(weight_max muni_code postal_code) str2 vehicle_type str1 vehicle_origin double kw_itv int co2 str9 emission str4 ev_type
-13355 "CITROEN"          "B2"             "0" 1452 10.99 1300 40904 40420 "40" "3" 14.72 . ""   ""
-12687 "CITROEN"          "5CV TYPE C"     "0"  855     8  900 28115 28224 "40" "0"   8.2 . "ND" ""
-12211 "ROCHET SCHNEIDER" "25000"          "0" 2603  15.6 2480  8078  8614 "40" "3"    24 . "ND" ""
-11801 "ERSKINE"          "MODEL 50 SEDAN" "0" 2394 17.45 1760 50298 50800 "40" "3" 29.82 . ""   ""
-10755 "FORD"             "A"              "0" 1200   .18    0 11020 11401 "40" "0"     0 . ""   ""
end
format %td fec_matricula_date

Here is a sample of my using dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input str8(fec_matricula fec_tramitacion) str23 brand str22 model_dup_original str1 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str17 emission str4(power ev_type)
"02012015" "05012015" "MAZDA"      "MAZDA 3"       "0" 1998  13.3 1800  5019  88 119 "EURO 5"   "0" ""
"02012015" "07012015" "RENAULT"    "MEGANE"        "0" 1197  9.78 1787 47186  97 124 "EURO 5J"  "0" ""
"02012015" "08012015" "MERCEDES"   "V 220 CDI"     "1" 2143 13.88 3050 17066 120 149 "EURO 5-K" "0" ""
"05012015" "09012015" "MERCEDES"   "V 250 BLUETEC" "1" 2143 13.88 3050 33066 140 157 "EURO 6W"  "0" ""
"02012015" "09012015" "VOLKSWAGEN" "GOLF"          "1" 1598 11.64 1820 47186  77  99 "ND"       "0" ""
end

I hope you can provide me some help on that please. I am really stuck since a while and do not know why.
Thanks a lot in advance!

[Clyde Schechter]

There is no such thing as a 1:n merge. There is 1:m, and a rarely used 1:_n. Please show the exact -merge- command you used. And you also need to provide more and better information about exactly how things went wrong. Pick example data that exhibits whatever problem you are encountering: the examples you have shown here don't look like they can be expected to match up at all, no matter how you did the -merge-. So show new examples where you expect matching but are not getting it, and then explain why you think you should be getting matches there.

[Michael Duarte Goncalves]

Good morning,

Sorry about the lack of details. Let me retry:


I have a dataset of Spanish vehicle registrations from this website (https://www.dgt.es/menusecundario/dg...s-mensual.html), which has been cleaned beforehand (the master dataset). We retain a key variable without cleaning (model_dup_duplicated) so that we can merge it with the raw data (the using dataset) and recover certain key variables that we previously lacked for our analysis, such as the zip code.

The second dataset is the raw data, without cleaning, to merge the variables we wanted (essentially the zip code). Both datasets come from the same source and should be the “same”, except that the master dataset is cleaned, and the one using is not.

Here are two examples for the same period and for the same brand and models:

1. Master file

* Example generated by -dataex-. For more info, type help dataex
clear
input str23 brand str39 model str22 model_dup_original str1 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str17 emission str4(power ev_type)
"VOLVO" "V 40"  "V40"  "1" 1969 13.19 1920  8002  88  82 "EURO 6W" "0" ""
"VOLVO" "V 40"  "V40"  "1" 1969 13.19 1920  2003  88  82 "EURO 6W" "0" ""
"VOLVO" "V 40"  "V40"  "1" 1969 13.19 1995  8002  88  97 "EURO 6W" "0" ""
"VOLVO" "V 40"  "V40"  "1" 1969 13.19 1995  9059 110 104 "EURO 6"  "0" ""
"VOLVO" "XC 60" "XC60" "1" 1969 13.19 2485 29067 110 117 "EURO 6"  "0" ""
end

2. Using file:

* Example generated by -dataex-. For more info, type help dataex
clear
input str29 brand str26 model_dup_original str2 fuel long engine_cap float fiscal_power long(weight_max muni_code) double kw_itv long postal_code str1 vehicle_origin int co2 str9 emission str4 ev_type
"VOLVO" "V40" "1" 1969 13.19 1920  8002  88  8256 "3"  82 "EURO 6W" ""
"VOLVO" "V40" "1" 1969 13.19 1920  2003  88  2007 "3"  82 "EURO 6W" ""
"VOLVO" "V40" "1" 1560 11.47 1890 28083  84 28880 "3"  94 "EURO 5"  ""
"VOLVO" "V40" "1" 1969 13.19 1995  8002  88  8256 "3"  97 "EURO 6W" ""
"VOLVO" "V40" "1" 1969 13.19 1995  9059 110  9004 "3" 104 "EURO 6"  ""
end

Here is my strategy, probably not the best: I want to delete duplicates on the master dataset, to be sure to use a merge 1:m

What I do:

duplicates drop new fec_matricula fec_tramitacion brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 ev_type, force

merge 1:m new fec_matricula_date fec_tramitacion_date brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 ev_type  using "`dgt_data_with_postal_code'", keepusing(postal_code) gen(merge_or_not)

But, as we can see, the results are not at all optimal. The data are not merged, despite be exactly the same, and I do not understand why:

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 new float(fec_matricula_date fec_tramitacion_date) str29 brand str26 model_dup_original str2 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str4 ev_type byte merge_or_not
"N" 20642 . "VOLVO" "V40" "1" 1969              13.19 1920  2003  88  82 "" 1
"N" 20642 . "VOLVO" "V40" "1" 1969              13.19 1920  8002  88  82 "" 1
"N" 20642 . "VOLVO" "V40" "1" 1969              13.19 1995  8002  88  97 "" 1
"N" 20642 . "VOLVO" "V40" "1" 1969              13.19 1995  9059 110 104 "" 1
"N" 20642 . "VOLVO" "V40" "1" 1969   13.1899995803833 1920  2003  88  82 "" 2
"N" 20642 . "VOLVO" "V40" "1" 1969   13.1899995803833 1920  8002  88  82 "" 2
"N" 20642 . "VOLVO" "V40" "1" 1969   13.1899995803833 1995  8002  88  97 "" 2
"N" 20642 . "VOLVO" "V40" "1" 1969   13.1899995803833 1995  9059 110 104 "" 2
"U" 20642 . "VOLVO" "V40" "1" 1560 11.470000267028809 1890 28083  84  94 "" 2
end
format %td fec_matricula_date
format %td fec_tramitacion_date
label values merge_or_not _merge
label def _merge 1 "Master only (1)", modify
label def _merge 2 "Using only (2)", modify

Thank you in advance for any feedback!

[Benno Schoenberger]

Unfortunately, I don't have the time to look into your problem in detail at the moment, but one obvious problem is the data types that are different for fiscal_power between the two datasets. If I just input your using dataset by

 
 input str29 brand str26 model_dup_original str2 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv long postal_code str1 vehicle_origin int co2 str9 emission str4 ev_type

then I can merge 4 of your 5 observations by

merge 1:1 brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code using `using'

So as a first impovement you have to make sure that both your variables from your master datset and your using dataset have identical storage types.

[Michael Duarte Goncalves]

Hi,

Thank you very much for your suggestion! Yes, data types are indeed different for those two variables.

I will try to transform the using variable fiscal_power from float to double, and see if I observe some changes!

I will run the code again. Thank you so much for your suggestion. I keep you updated.

Michael

[Michael Duarte Goncalves]

I obtained exactly the same result, despite changing in using dataset the variable fiscal_power to double


The changes made do not seem to appear, and I do not understand why:

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 new str8(fec_matricula fec_tramitacion) str29 brand str2 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str17 emission str4 ev_type str26 model_dup_original float fec_matricula_date byte merge_or_not
"N" "07072016" "0" "VOLVO" "1" 1969              13.19 1920  8002  88  82 "EURO 6W" "" "V40" 20642 1
"N" "07072016" "0" "VOLVO" "1" 1969              13.19 1920  2003  88  82 "EURO 6W" "" "V40" 20642 1
"N" ""         ""  "VOLVO" "1" 1969   13.1899995803833 1920  8002  88  82 ""        "" "V40" 20642 2
"N" ""         ""  "VOLVO" "1" 1969   13.1899995803833 1920  2003  88  82 ""        "" "V40" 20642 2
"U" ""         ""  "VOLVO" "1" 1560 11.470000267028809 1890 28083  84  94 ""        "" "V40" 20642 2
"N" "07072016" "0" "VOLVO" "1" 1969              13.19 1995  8002  88  97 "EURO 6W" "" "V40" 20642 1
"N" ""         ""  "VOLVO" "1" 1969   13.1899995803833 1995  8002  88  97 ""        "" "V40" 20642 2
"N" "07072016" "0" "VOLVO" "1" 1969              13.19 1995  9059 110 104 "EURO 6"  "" "V40" 20642 1
"N" ""         ""  "VOLVO" "1" 1969   13.1899995803833 1995  9059 110 104 ""        "" "V40" 20642 2
end
format %td fec_matricula_date
label values merge_or_not _merge
label def _merge 1 "Master only (1)", modify
label def _merge 2 "Using only (2)", modify

I do not know if this can have an impact on the final result, but I used a tempfile for the using dataset. Here my code snippet:

clear all
use "${data}/raw_data/DGT_data_raw/csv/DGT_data_201412_202407.dta", clear

generate double fiscal_power_new = fiscal_power

drop fiscal_power

rename fiscal_power_new fiscal_power

// Define a local with the old variable names
local old_vars fec_matricula_date fec_tramitacion_date marca_itv modelo_itv cod_procedencia_itv cod_tipo cod_propulsion_itv cilindrada_itv potencia_itv codigo_postal ind_nuevo_usado cod_municipio_ine_veh kw_itv co2_itv nivel_emisiones_euro_itv categoría_vehículo_eléctrico peso_max

// Define a local with the new variable names
local new_vars fec_matricula_date fec_tramitacion_date brand model vehicle_origin vehicle_type fuel engine_cap fiscal_power postal_code new muni_code kw_itv co2 emission ev_type weight_max

// Loop over the variables and rename
forval i = 1/17 {
    local old : word `i' of `old_vars'
    local new : word `i' of `new_vars'
    rename `old' `new'
}

gen model_dup_original = itrim(trim(model))

drop model

generate double fiscal_power_new = fiscal_power

drop fiscal_power

rename fiscal_power_new fiscal_power

keep if inrange(fec_matricula_date, td(31dec2014), td(30dec2019))

tempfile dgt_data_with_postal_code

save `dgt_data_with_postal_code', replace

and for the merge, here is the code:

clear*
use "${data}/append_cleaned_cardesc15-19_fordemand/all_cars_cleaned_cardesc15-19_fordemand.dta", clear

rename _all, lower

keep new fec_matricula fec_tramitacion fec_matricula_date fec_tramitacion_date brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 emission ev_type

duplicates drop new fec_matricula fec_tramitacion brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 ev_type, force

merge 1:m new fec_matricula_date fec_tramitacion_date brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 ev_type  using "`dgt_data_with_postal_code'", keepusing(postal_code) gen(merge_or_not)

Thank you again for your help!

[Benno Schoenberger]

I have to apologize for not being clear enough in my previous post. Variable fiscal_power appears at first glance to have the same values in your two datasets, but since they are stored differently, they are not strictly speaking identical for Stata and thus cannot be merged correctly.
The following example perhaps illustrates this a little better:

clear
input double varA float varB
13.19    13.19
11.47    11.47
end

assert varA == varB
2 contradictions in 2 observations
assertion is false
r(9);
// generating a new double variable by rounding varB to two digits
gen double varC = round(varB,.01)

assert varA == varC

So if you apply this to your example, you could simply recreate your variable fiscal_power in the master data set:

gen double new = round(fiscal_power,.01)
rename (fiscal_power new) (something fiscal_power)

I've played around with similar data before and encountered a lot of little pitfalls like this, so I'm relatively sure that your data set will also have a few more hurdles to overcome.

[Clyde Schechter]

First, your list of variables in -duplicates drop- and in --merge- includes variables that are not in your example data sets. These are new, fec_matricula_date, and fec_tramitacion_date. So I can't really try to replicate your problem with the examples. I suppose that -dataex- would not accommodate all of your variables, and you chose to omit a few.

Nevertheless, I tried to replicate your difficulties omitting those variables from the commands. And it is certainly true that nothing matches across the two data sets in this case. But that is as it should be. Let's take a detailed look:

Observation 5 in the master data set has model_dup_original XC60, which does not match anything in the using data.

If you were to use just model_dup_original engine_cap fiscal_power weight_max as your -merge- key variables, you would find matches for observations 1 through 4 of the master data set in the using data set. But once you add in muni_code, you get no matches at all. After working with the example data for a while, I have found that the largest subset of the example variables that can be used as the -merge- key and still find at least one match in the example data is brand model_dup_original fuel engine_cap fiscal_power weight_max kw_itv co2 ev_type. Adding any of the other variables to this leaves no matches at all.

As an aside, let me point out that when you use -duplicates drop whatever, force- you are discarding information. Observations that agree on the whatever variables but differ on other things will end up having a single representative from that group of observations retained--and its values on the disagreeing variables will represent an arbitrary selection from your data. If those disagreeing variables really don't matter for what you will be doing going forward, then it is better to just -drop- those variables altogether before you do -duplicates drop whatever-. That will safely accomplish what you are trying to do with -duplicates drop whatever, force- but will not mistakenly create a data set with incomplete and potentially erroneous information. -force- options, in any command that has them, are always dangerous and should almost never be used.

[Michael Duarte Goncalves]

Thank you very much to both! Really appreciated help!

The matched make-models increased significantly with your suggestion in post #7. Thank you very much. But the merge is still not perfect. My master dataset has a total of 4,000,414 observations. I do not understand why I obtained those numbers, please:

    Result                      Number of obs
    -----------------------------------------
    Not matched                     3,350,634
        from master                   364,068  (merge_or_not==1)
        from using                  2,986,566  (merge_or_not==2)

    Matched                         3,697,439  (merge_or_not==3)
    -----------------------------------------

If I am not mistaken, the sum of "Not matched > from master" and the "Matched" ones should be equal to 4,000,414. And that is not the case.

Would you happen to know why this is happening?

Thanks a lot.
Michael

[Michael Duarte Goncalves]

Then I have a question related with post #8, please:

As an aside, let me point out that when you use -duplicates drop whatever, force- you are discarding information. Observations that agree on the whatever variables but differ on other things will end up having a single representative from that group of observations retained--and its values on the disagreeing variables will represent an arbitrary selection from your data. If those disagreeing variables really don't matter for what you will be doing going forward, then it is better to just -drop- those variables altogether before you do -duplicates drop whatever-. That will safely accomplish what you are trying to do with -duplicates drop whatever, force- but will not mistakenly create a data set with incomplete and potentially erroneous information. -force- options, in any command that has them, are always dangerous and should almost never be used.

I completely understand your sentence. However then, how could I find a way of merging my 4,000,414 observations correctly without having to use duplicates drop whatever, force in order to have a 1 and be able to do a merge 1:m

I don't really know of any alternatives, and would be happy to learn a little more from the expertise of Statalist members.
Again, thank you very much again for taking the time to reply.

[Benno Schoenberger]

Dear Michael,
We neither have access to your raw data nor do we know exactly every single part of your syntax, so we can only speculate as to why you arrive at these numbers. Before asking such questions, you should first ask yourself whether you are providing the members of this forum with enough information to answer them.
Basically, I agree with you that the number of matched and unmatched cases that came from the master data set should correspond to the total number of master cases. If they don't, it's likely that you're taking some data preparation steps (such as droppping duplicates) that are changing the number of observations.

[Michael Duarte Goncalves]

Ok, I am sorry about that Benno. I do not want to bore you with all my code, probably not very well done.

Basically, here is what I tried, from scratch:

* "Data Cleaning cardesc15-19_fordemand.dta"
* LEZ Project: -merge- brands and models, cleaned, to have the final cleaned database
* September 2024*
********************************************************************************
clear all
version 17
set more off
set varabbrev off


* Settings specific to local environment (adapt that to your current path)
// cd path
global data "C:/Users/miduarte/Desktop/Ongoing_Projects/LEZ_Project/Data/stata/0_demand"
cd "${data}"

// import delimited using "${data}/raw_data/DGT_data_raw/csv/DGT_data_201412_202407.csv", clear varnames(1) case(lower)
//
// save "${data}/raw_data/DGT_data_raw/csv/DGT_data_201412_202407.dta", replace

**
clear all
use "${data}/raw_data/DGT_data_raw/csv/DGT_data_201412_202407.dta", clear

*******************************************************************************
* Proper date variable creation + keep some key data

keep if inlist(cod_tipo, "25", "40")

tostring fec_matricula fec_tramitacion, replace format(%20.0f)

replace fec_matricula = "0" + fec_matricula if length(fec_matricula) == 7

replace fec_tramitacion = "0" + fec_tramitacion if length(fec_tramitacion) == 7

gen fec_matricula_date = date(fec_matricula, "DMY")

format fec_matricula_date %td

gen fec_tramitacion_date = date(fec_tramitacion, "DMY")

format fec_tramitacion_date %td
*******************************************************************************
* Some data cleaning to preserve some key variables + renaming ones

// keep those variables
keep fec_matricula_date fec_tramitacion_date fec_matricula fec_tramitacion marca_itv modelo_itv cod_procedencia_itv cod_tipo cod_propulsion_itv cilindrada_itv potencia_itv codigo_postal cod_municipio_ine_veh ind_nuevo_usado kw_itv co2_itv nivel_emisiones_euro_itv categoría_vehículo_eléctrico peso_max

replace marca_itv  = itrim(trim(marca_itv))

replace modelo_itv = itrim(trim(modelo_itv))

chartab kw_itv // contains some kw_itv = "**********"

replace kw_itv = "" if strpos(kw_itv, "*") > 0

destring kw_itv, replace

// Define a local with the old variable names. This is to have the same variable names as my main dataset
local old_vars fec_matricula_date fec_tramitacion_date marca_itv modelo_itv cod_procedencia_itv cod_tipo cod_propulsion_itv cilindrada_itv potencia_itv codigo_postal ind_nuevo_usado cod_municipio_ine_veh kw_itv co2_itv nivel_emisiones_euro_itv categoría_vehículo_eléctrico peso_max

// Define a local with the new variable names
local new_vars fec_matricula_date fec_tramitacion_date brand model vehicle_origin vehicle_type fuel engine_cap fiscal_power postal_code new muni_code kw_itv co2 emission ev_type weight_max

// Loop over the variables and rename
forval i = 1/17 {
    local old : word `i' of `old_vars'
    local new : word `i' of `new_vars'
    rename `old' `new'
}

// We want to merge this key variable with master. But have to trim spaces
gen model_dup_original = itrim(trim(model))

drop model

generate double fiscal_power_new = round(fiscal_power, .01)

drop fiscal_power

rename fiscal_power_new fiscal_power

keep if inrange(fec_matricula_date, td(31dec2014), td(30dec2019))

tempfile dgt_data_with_postal_code

save `dgt_data_with_postal_code', replace
******************************************************************************

* use the cleaned data, with all data cleaned (brands cleaned, models cleaned).
clear*
use "${data}/append_cleaned_cardesc15-19_fordemand/all_cars_cleaned_cardesc15-19_fordemand.dta", clear

rename _all, lower // have the same variable names as in my using dataset above

******************************************************************************
* Date variable creation

tostring fec_matricula fec_tramitacion, replace

gen day = substr(fec_matricula, 1, 2)      

gen month = substr(fec_matricula, 3, 2)  

gen year = substr(fec_matricula, 5, 4)  

destring day month year, replace

gen fec_matricula_date = mdy(month, day, year)
      
format fec_matricula_date %td

drop day month year

******************************************************************************
* Date variable creation          

gen day = substr(fec_tramitacion, 1, 2)      

gen month = substr(fec_tramitacion, 3, 2)  

gen year = substr(fec_tramitacion, 5, 4)    

destring day month year, replace

gen fec_tramitacion_date = mdy(month, day, year)
      
format fec_tramitacion_date %td          

drop day month year

********************************************************************************

keep new fec_matricula fec_tramitacion fec_matricula_date fec_tramitacion_date brand model_dup_original fuel engine_cap fiscal_power weight_max muni_code kw_itv co2 emission ev_type

duplicates drop new fec_matricula_date fec_tramitacion_date model_dup_original fuel engine_cap fiscal_power weight_max muni_code co2 ev_type, force

merge 1:m new fec_matricula_date fec_tramitacion_date model_dup_original fuel engine_cap fiscal_power weight_max muni_code co2 ev_type  using "`dgt_data_with_postal_code'", keepusing(postal_code) gen(merge_or_not)

exit, clear

Sorry if it is not well-coded from the beginning.
Thanks again for your help and patience.

[Michael Duarte Goncalves]

Here again, I'm expecting 5 merges, given that everything seems to be similar... or at least visually.

That is strange, because I put fiscal_power as double as recommended by Benno.

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 new str2 fuel long engine_cap double fiscal_power long(weight_max muni_code) double kw_itv int co2 str17 emission str4 ev_type str26 model_dup_original float(fec_matricula_date fec_tramitacion_date) long postal_code byte merge_or_not
"N" "1" 1995              13.29 2025 28065 110 118 "EURO 5J"  "" "LAGUNA" 20090 20464     . 1
"N" "1" 1995              13.29 2025 46201 110 118 "EURO 5J"  "" "LAGUNA" 20090 20465     . 1
"N" "1" 1995              13.29 2076 28125 127 150 "EURO 5 J" "" "LAGUNA" 20090     .     . 1
"N" "1" 1995              13.29 2025 28125 110 118 "EURO 5J"  "" "LAGUNA" 20090     .     . 1
"N" "1" 1995              13.29 2025 47186  96 118 "EURO 5J"  "" "LAGUNA" 20090 20114     . 1
"N" "1" 1995 13.290000000000001 2076 28125 127 150 ""         "" "LAGUNA" 20090     . 28294 2
"U" "1" 1995               13.3 2012  3014  96 157 ""         "" "LAGUNA" 20090     .  3013 2
"N" "1" 1995 13.290000000000001 2025 28125 110 118 ""         "" "LAGUNA" 20090     . 28294 2
"N" "1" 1995 13.290000000000001 2025 46201 110 118 ""         "" "LAGUNA" 20090 20465 46140 2
"N" "1" 1995 13.290000000000001 2025 28065 110 118 ""         "" "LAGUNA" 20090 20464 28904 2
"N" "1" 1995 13.290000000000001 2025 47186  96 118 ""         "" "LAGUNA" 20090 20114 47014 2
end
format %td fec_matricula_date
format %td fec_tramitacion_date
label values merge_or_not _merge
label def _merge 1 "Master only (1)", modify
label def _merge 2 "Using only (2)", modify

[Clyde Schechter]

If I am not mistaken, the sum of "Not matched > from master" and the "Matched" ones should be equal to 4,000,414.

You are mistaken. This would be true for a 1:1 match. But in a 1:m match, an observation in the master data set that matches to multiple observations in the using data set will end up contributing more than 1 observation to the final count in the merged data. So for a 1:m match, the "accounting identity" would be matched + master only >= original # of observations in the master data set.

[quote]I completely understand your sentence. However then, how could I find a way of merging my 4,000,414 observations correctly without having to use duplicates drop whatever, force in order to have a 1 and be able to do a merge 1:m[quote]
When I wrote that in #8, I explained the alternative. But I'll elaborate on it further here. You need to know where you are going to go with the -merge-d data set. Identify which of the variables in the master data will be needed after the -merge-. Since they are going to be needed, it is important that you have the correct values for them. If the overall logic of your project requires a 1:m merge, then that also means that all of the observations having the same given value on the merge key variables must also agree on the other variable that you will need after the merge. If that isn't true, then reducing to 1 observation each will create a corrupted data set. So, once you have identified the merge key variable plus all of the variables that will be needed after the -merge-, you should -drop- all the other variables. Then run -duplicates drop- without the -force- option, and without mentioning specific variables.

When you do that, if all of your retained variables have the same values for all observations in a merge key group, then duplicates on the merge key are also duplicates on everything, so your -duplicates drop- command does exactly what you need and the -merge 1:m merge_key- will proceed uneventfully.

If, on the other hand, it turns out that some of the variables you retained have conflicting values within a merge key group, then they will not be recognized as duplicates, and they will not be dropped. Then when you attempt the -merge 1:m merge_key-, Stata will see that there are some merge keys that are still duplicated in the data set and will tell you that your merge key does not uniquely identify observations in the master data. At that point, you are in the same situation as anybody else who encounters this error. You expected all the retained variables to be constant within merge key groups, but they aren't. So you have to find the offending observations and figure out why they are there and what the proper way to correct that is.

[Michael Duarte Goncalves]

Hi,

Thank you for your advices and your time in elaborating the answer! Everything is clear now!
I will try to do what you wrote... let see!


Many thanks to Benno Schoenberger and Clyde Schechter for your time and help, above all!
Michael