how to merge two files 1:m or m:1 with 7 key variables

[Abelardo Rodriguez]

I am trying to merge two files from INEGI, Mexico, Infant Labor Module 2017. According to INEGI there are seven key variables required to successfully merge two data sets (89,000 and 52,000 observations with almost 200 combined variables). I have tried to execute both merge m:1 and 1:m options without success. Only 82 cases with merge==3 !!
The STATA manual for Merge provides one example of merge 1:1 with two key variables but the examples provided for merge 1:m or m:1 include only one key variable. However, the Stata manual mentions that the "key variable or variables uniquely identify observations in the using data or master data set." Does Stata have a limit on how many key variables the Merge command can successfully execute?

[Clyde Schechter]

Does Stata have a limit on how many key variables the Merge command can successfully execute?

No, it does not. There are some limitations that arise from things like an overall limit on the length of a command. And there is a limit on how many variables a data set can have (which depends on whether you are running IC, SE, or MP.) But with 7 merge key variables there is no way that either of those limits is even close to biting you.

In order to get concrete help with this, you should post examples from both data sets (use -dataex-) and also show the exact -merge- commands you have tried. When showing example data, it would be most helpful if your examples include observations that you believe should be matching with each other but that Stata doesn't match them.

If you are running version 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.

Without seeing those, I can tell you that a common cause of the problem you are encountering is when "the same variable" in the two data sets is coded differently. Remember that -merge- will enforce exact equality on all 7 variables to declare a match. Remember that "ABC", "abc", "Abc", " abc",and "abc " are all completely different values as far as this goes. So if any of the merge key variables are strings, you need to worry about upper vs lower case, and the possibility of leading, trailing, or embedded blanks messing things up. Spelling errors (or even correct variations, "labor" vs "labour") are another common obstacle to -merge-. And with numeric variables, numbers that look alike to our eyes may differ in actual value if they have different storage types. And don't forget about the potential for value-labeled numeric variables to look like they match because they have the same label showing, but have different underlying numeric values. Numeric variables that look alike can also fail to match if their display formats show only a certain number of decimal places, but they differ in lower order places that are not shown.

[William Lisowski]

Welcome to Statalist.

There is no documented limit on the number of key variables that can be used in merge, and if there were a limit it is almost certain that Stata would not proceed with the merge if the limit were exceeded.

So the problem is likely with your data. I would recommend that you sort each of your datasets by the 7 key variables, and then use the dataex command to present examples of the 7 key variables in each dataset.

use dataset1, clear
sort key1 key2 key3 key4 key5 key6 key7
dataex key1 key2 key3 key4 key5 key6 key7

use dataset2, clear
sort key1 key2 key3 key4 key5 key6 key7
dataex key1 key2 key3 key4 key5 key6 key7

And then copy the output from the two dataex commands and paste it into a new reply on this topic. Perhaps that will be sufficient for someone to see where the problem lies.

[Abelardo Rodriguez]

Thanks, I will get back after I do some work.

[Abelardo Rodriguez]

Finally, I got everything using dataex

1. printed first 100 cases in DATA 1 unsorted
2. printed first 100 cases in DATA 1 sorting 7 variables
3. printed first 100 cases in DATA 2 unsorted
4. printed first 100 cases in DATA2 sorting 7 key variables
I could not find any visual problems with the lists of numbers.
5. there are two merges 1:m and m:1. Both gave the same result, as one would expect.
6. I listed the 7 key variables that matched (merge==3)
7. My conclusion is that STATA is doing proper matches over seven key variables. However, how to approach the overall results if I am using microdata from the government agency that put it together.

Thanks in advance for your comments.


. do "/Users/Abelardo/OneDrive/A ARL papers/ST wk/debug 5 oct 19.do"

.
. set more off

.
. use "/Users/Abelardo/OneDrive/A ARL papers/ST wk/mti2017_sdem.dta", clear

. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren, varlabel count(100)

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
9 40001   9 1 1 0  3
9 40001   9 1 1 0  4
9 40001  65 3 1 0  3
9 40001  90 4 1 0  3
9 40003 103 3 1 0  4
9 40003 171 5 1 0  3
9 40004   7 1 1 0  3
9 40004  49 3 1 0  3
9 40004  91 5 1 0  3
9 40004  91 5 1 0  4
9 40005  88 2 1 0  3
9 40005  88 2 1 0  4
9 40005  88 2 1 0  5
9 40005  28 5 1 0  2
9 40005  28 5 1 0  3
9 40006  90 4 1 0  3
9 40006  90 4 1 0  4
9 40010  66 2 1 0  3
9 40010  66 2 1 0  4
9 40010   7 3 1 0  2
9 40010  37 5 1 0  3
9 40011   3 1 1 0  5
9 40011  16 2 1 0  3
9 40011  16 2 1 0  4
9 40011  29 3 1 0  4
9 40011  29 3 1 0  6
9 40011  61 5 1 0  5
9 40011  61 5 1 0  6
9 40012  43 1 1 0  3
9 40012  43 1 1 0  4
9 40012  74 2 1 0  6
9 40013  28 1 1 0  3
9 40013 107 5 1 0  4
9 40014 147 2 1 0  5
9 40014  23 3 1 0  3
9 40014  23 3 1 0  4
9 40015  10 1 1 0  3
9 40016  90 2 1 0  3
9 40016  90 2 1 0  4
9 40016  21 3 1 0  5
9 40016  21 3 1 0  6
9 40016  40 4 1 0  5
9 40016  61 5 1 0  3
9 40016  61 5 1 0  4
9 40017   1 3 1 0  3
9 40017  29 4 1 0  5
9 40018  86 2 1 0  3
9 40022 104 3 1 0  2
9 40022 104 3 1 0  3
9 40022 104 3 2 0  3
9 40022 104 3 2 0  4
9 40022 104 3 2 0  5
9 40022 104 3 2 0  6
9 40022 144 4 1 0  3
9 40022 144 4 1 0  4
9 40023  33 4 1 0  3
9 40023  33 4 1 0  4
9 40025  47 2 1 0  3
9 40025  47 2 1 0  4
9 40026  50 2 1 0  3
9 40026 125 5 1 0  3
9 40027 132 1 1 0  3
9 40027 132 1 1 0  4
9 40027 132 1 1 0  5
9 40027  47 2 1 0  3
9 40027  88 3 1 0  3
9 40027  88 3 1 0  4
9 40027   5 5 1 0  4
9 40029   5 1 1 0  4
9 40030  54 1 1 0  4
9 40030  54 1 1 0  5
9 40030 125 5 1 0  3
9 40032  11 1 1 0  2
9 40032  11 1 1 0  3
9 40032  81 3 1 0  3
9 40032  81 3 1 0  4
9 40032  81 3 1 0  5
9 40033  15 3 1 0  3
9 40033  15 3 1 0  5
9 40033  44 4 1 0  4
9 40034  96 5 1 0  6
9 40035  33 2 1 1  3
9 40035  76 4 1 0  3
9 40036  27 2 1 0  3
9 40036  27 2 1 0  4
9 40037  19 1 1 0  4
9 40037  47 2 1 0  5
9 40037  47 2 1 0  6
9 40037  47 2 1 0  9
9 40037  47 2 1 0 10
9 40040 130 3 1 0  4
9 40041  43 2 4 0  3
9 40041  87 4 1 0  3
9 40041  87 4 1 0  4
9 40041  87 4 1 0  5
9 40042  89 5 1 0  4
9 40042  89 5 1 0  5
9 40042  89 5 1 0  6
9 40043  75 1 1 0  3
9 40043 126 2 1 0  4
end
label var ent "ENT"
label var con "CON"
label var n_pro_viv "N_PRO_VIV"
label var v_sel "V_SEL"
label var n_hog "N_HOG"
label var h_mud "H_MUD"
label var n_ren "N_REN"

------------------ copy up to and including the previous line ------------------

Listed 100 out of 89521 observations

. sort ent con n_pro_viv v_sel n_hog h_mud n_ren

. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren if ent==1, varlabel count(100)

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
1 40001  26 5 1 0 3
1 40001  85 4 1 0 3
1 40001  97 1 1 0 3
1 40001  97 1 1 0 4
1 40001  97 1 1 0 5
1 40001  97 1 1 0 6
1 40002  77 4 1 0 4
1 40002  98 5 1 0 6
1 40003  10 1 1 0 3
1 40003  58 5 1 0 3
1 40003  98 3 1 0 3
1 40003  98 3 1 0 4
1 40004  43 5 1 0 3
1 40004  43 5 1 0 4
1 40004  65 1 1 0 4
1 40004 117 3 1 0 4
1 40004 117 3 1 0 5
1 40004 117 3 1 0 6
1 40005   8 1 1 0 4
1 40005  64 3 1 0 4
1 40005  64 3 1 0 5
1 40006  13 5 1 0 3
1 40006  13 5 1 0 4
1 40006  71 2 1 0 3
1 40006  71 2 1 0 4
1 40006  71 2 1 0 5
1 40006  88 3 1 0 5
1 40007   6 4 1 0 3
1 40007  36 5 1 0 4
1 40007  59 1 1 0 4
1 40007  59 1 1 0 5
1 40010  40 5 1 0 5
1 40010  58 2 1 0 3
1 40011  22 1 1 0 4
1 40011  81 4 1 0 3
1 40011 110 5 1 0 4
1 40011 110 5 1 0 5
1 40012  38 3 1 0 4
1 40012  38 3 1 0 5
1 40012  38 3 1 0 6
1 40012  38 3 1 0 7
1 40015  24 1 1 0 4
1 40015  24 1 1 0 5
1 40015  24 1 1 0 6
1 40015  87 5 1 0 3
1 40015  87 5 1 0 4
1 40015  87 5 1 0 5
1 40015  87 5 1 0 9
1 40015  90 4 1 0 3
1 40016  43 4 1 0 3
1 40016  43 4 1 0 4
1 40016  58 2 1 0 3
1 40016  58 2 1 0 4
1 40016  82 3 1 0 3
1 40016  82 3 1 0 4
1 40016  82 3 1 0 5
1 40016  84 1 1 0 3
1 40017  23 3 1 0 3
1 40017  23 3 1 0 4
1 40017  23 3 1 0 6
1 40017  23 3 1 0 7
1 40017 122 1 1 0 3
1 40017 154 2 1 0 3
1 40018  98 2 1 0 5
1 40018 126 3 1 0 3
1 40018 126 3 1 0 4
1 40018 126 3 1 0 5
1 40019  13 1 1 0 3
1 40019  13 1 1 0 5
1 40019  13 1 1 0 6
1 40019  81 4 1 0 4
1 40019  81 4 1 0 5
1 40019  81 4 1 0 6
1 40019  97 5 1 0 3
1 40019  97 5 1 0 4
1 40020  14 2 1 0 6
1 40020  51 1 1 0 3
1 40021   4 1 1 0 3
1 40021   4 1 1 0 4
1 40021  36 2 1 0 4
1 40021  36 2 1 0 5
1 40021  36 2 1 0 6
1 40021 100 4 1 0 2
1 40021 100 4 1 0 3
1 40021 127 5 1 0 2
1 40022  62 5 1 0 2
1 40022 117 3 1 0 5
1 40022 122 4 1 0 3
1 40022 122 4 1 0 4
1 40023  19 1 1 0 4
1 40023  19 1 1 0 5
1 40023  19 1 1 0 6
1 40025   4 5 1 0 3
1 40025   4 5 1 0 4
1 40025  13 1 1 0 2
1 40025  13 1 1 0 5
1 40025  13 1 1 0 6
1 40025  52 3 1 0 3
1 40025  52 3 1 0 4
1 40026 127 3 1 0 3
end
label var ent "ENT"
label var con "CON"
label var n_pro_viv "N_PRO_VIV"
label var v_sel "V_SEL"
label var n_hog "N_HOG"
label var h_mud "H_MUD"
label var n_ren "N_REN"

------------------ copy up to and including the previous line ------------------

Listed 100 out of 89521 observations

.
. use "/Users/Abelardo/OneDrive/A ARL papers/ST wk/mti2017_hog.dta", clear

. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren, varlabel count(100)

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
1 40001  97 1 1 0 1
1 40001  85 4 1 0 1
1 40001  26 5 1 0 1
1 40002  77 4 1 0 1
1 40002  98 5 1 0 1
1 40003  10 1 1 0 1
1 40003  98 3 1 0 1
1 40003  58 5 1 0 1
1 40004  65 1 1 0 1
1 40004 117 3 1 0 1
1 40004  43 5 1 0 1
1 40005   8 1 1 0 1
1 40005  64 3 1 0 1
1 40006  71 2 1 0 1
1 40006  88 3 1 0 1
1 40006  13 5 1 0 1
1 40007  59 1 1 0 1
1 40007   6 4 1 0 1
1 40007  36 5 1 0 1
1 40010  58 2 1 0 1
1 40010  40 5 1 0 1
1 40011  22 1 1 0 1
1 40011  81 4 1 0 1
1 40011 110 5 1 0 1
1 40012  38 3 1 0 1
1 40015  24 1 1 0 1
1 40015  90 4 1 0 1
1 40015  87 5 1 0 1
1 40016  84 1 1 0 1
1 40016  58 2 1 0 1
1 40016  82 3 1 0 2
1 40016  43 4 1 0 1
1 40017 122 1 1 0 1
1 40017 154 2 1 0 1
1 40017  23 3 1 0 1
1 40018  98 2 1 0 1
1 40018 126 3 1 0 1
1 40019  13 1 1 0 1
1 40019  81 4 1 0 1
1 40019  97 5 1 0 1
1 40020  51 1 1 0 1
1 40020  14 2 1 0 1
1 40021   4 1 1 0 1
1 40021  36 2 1 0 1
1 40021 100 4 1 0 1
1 40021 127 5 1 0 1
1 40022 117 3 1 0 2
1 40022 122 4 1 0 1
1 40022  62 5 1 0 1
1 40023  19 1 1 0 1
1 40025  13 1 1 0 1
1 40025  52 3 1 0 2
1 40025   4 5 1 0 1
1 40026 127 3 1 0 1
1 40028  27 2 1 0 1
1 40028  50 3 1 0 1
1 40028 100 5 1 0 1
1 40029  17 1 1 0 1
1 40029  34 2 1 0 1
1 40029  90 4 1 0 1
1 40030  75 4 1 0 1
1 40031  65 1 1 0 1
1 40031  22 3 1 0 1
1 40031  45 4 1 0 1
1 40032  44 2 1 0 1
1 40033   8 1 1 0 1
1 40033  31 2 1 0 1
1 40034   1 1 1 0 1
1 40034  26 2 1 0 1
1 40034  95 5 1 0 1
1 40035  73 1 1 0 1
1 40036  87 2 1 0 1
1 40036  37 4 1 0 1
1 40036 113 5 1 0 1
1 40037   7 1 1 0 1
1 40037  28 2 1 0 1
1 40037  70 4 1 0 1
1 40037  87 5 1 0 1
1 40038 106 1 1 0 1
1 40038  67 3 1 0 1
1 40038   9 4 1 0 1
1 40038  34 5 1 0 1
1 40039  54 2 1 0 2
1 40039  23 5 1 1 1
1 40040  33 4 1 0 1
1 40040 103 5 1 0 1
1 40041  74 1 1 0 1
1 40042  15 1 1 0 1
1 40042  34 2 1 0 1
1 40042  59 3 1 0 1
1 40042  67 4 1 0 1
1 40042  88 5 1 0 1
1 40043  82 2 1 0 1
1 40043 105 3 1 0 1
1 40043  13 4 1 0 1
1 40043  34 5 1 0 1
1 40044  11 1 1 0 1
1 40045  67 1 1 0 1
1 40045   8 4 1 0 1
1 40045  22 5 1 0 1
end
label var ent "ENT"
label var con "CON"
label var n_pro_viv "N_PRO_VIV"
label var v_sel "V_SEL"
label var n_hog "N_HOG"
label var h_mud "H_MUD"
label var n_ren "N_REN"

------------------ copy up to and including the previous line ------------------

Listed 100 out of 52181 observations

. sort ent con n_pro_viv v_sel n_hog h_mud n_ren

. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren, varlabel count(100)

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
1 40001  26 5 1 0 1
1 40001  85 4 1 0 1
1 40001  97 1 1 0 1
1 40002  77 4 1 0 1
1 40002  98 5 1 0 1
1 40003  10 1 1 0 1
1 40003  58 5 1 0 1
1 40003  98 3 1 0 1
1 40004  43 5 1 0 1
1 40004  65 1 1 0 1
1 40004 117 3 1 0 1
1 40005   8 1 1 0 1
1 40005  64 3 1 0 1
1 40006  13 5 1 0 1
1 40006  71 2 1 0 1
1 40006  88 3 1 0 1
1 40007   6 4 1 0 1
1 40007  36 5 1 0 1
1 40007  59 1 1 0 1
1 40010  40 5 1 0 1
1 40010  58 2 1 0 1
1 40011  22 1 1 0 1
1 40011  81 4 1 0 1
1 40011 110 5 1 0 1
1 40012  38 3 1 0 1
1 40015  24 1 1 0 1
1 40015  87 5 1 0 1
1 40015  90 4 1 0 1
1 40016  43 4 1 0 1
1 40016  58 2 1 0 1
1 40016  82 3 1 0 2
1 40016  84 1 1 0 1
1 40017  23 3 1 0 1
1 40017 122 1 1 0 1
1 40017 154 2 1 0 1
1 40018  98 2 1 0 1
1 40018 126 3 1 0 1
1 40019  13 1 1 0 1
1 40019  81 4 1 0 1
1 40019  97 5 1 0 1
1 40020  14 2 1 0 1
1 40020  51 1 1 0 1
1 40021   4 1 1 0 1
1 40021  36 2 1 0 1
1 40021 100 4 1 0 1
1 40021 127 5 1 0 1
1 40022  62 5 1 0 1
1 40022 117 3 1 0 2
1 40022 122 4 1 0 1
1 40023  19 1 1 0 1
1 40025   4 5 1 0 1
1 40025  13 1 1 0 1
1 40025  52 3 1 0 2
1 40026 127 3 1 0 1
1 40028  27 2 1 0 1
1 40028  50 3 1 0 1
1 40028 100 5 1 0 1
1 40029  17 1 1 0 1
1 40029  34 2 1 0 1
1 40029  90 4 1 0 1
1 40030  75 4 1 0 1
1 40031  22 3 1 0 1
1 40031  45 4 1 0 1
1 40031  65 1 1 0 1
1 40032  44 2 1 0 1
1 40033   8 1 1 0 1
1 40033  31 2 1 0 1
1 40034   1 1 1 0 1
1 40034  26 2 1 0 1
1 40034  95 5 1 0 1
1 40035  73 1 1 0 1
1 40036  37 4 1 0 1
1 40036  87 2 1 0 1
1 40036 113 5 1 0 1
1 40037   7 1 1 0 1
1 40037  28 2 1 0 1
1 40037  70 4 1 0 1
1 40037  87 5 1 0 1
1 40038   9 4 1 0 1
1 40038  34 5 1 0 1
1 40038  67 3 1 0 1
1 40038 106 1 1 0 1
1 40039  23 5 1 1 1
1 40039  54 2 1 0 2
1 40040  33 4 1 0 1
1 40040 103 5 1 0 1
1 40041  74 1 1 0 1
1 40042  15 1 1 0 1
1 40042  34 2 1 0 1
1 40042  59 3 1 0 1
1 40042  67 4 1 0 1
1 40042  88 5 1 0 1
1 40043  13 4 1 0 1
1 40043  34 5 1 0 1
1 40043  82 2 1 0 1
1 40043 105 3 1 0 1
1 40044  11 1 1 0 1
1 40045   8 4 1 0 1
1 40045  22 5 1 0 1
1 40045  67 1 1 0 1
end
label var ent "ENT"
label var con "CON"
label var n_pro_viv "N_PRO_VIV"
label var v_sel "V_SEL"
label var n_hog "N_HOG"
label var h_mud "H_MUD"
label var n_ren "N_REN"

------------------ copy up to and including the previous line ------------------

Listed 100 out of 52181 observations

.
. merge 1:m ent con n_pro_viv v_sel n_hog h_mud n_ren using ///
> "/Users/Abelardo/OneDrive/A ARL papers/ST wk/mti2017_sdem.dta"

Result # of obs.
-----------------------------------------
not matched 141,538
from master 52,099 (_merge==1)
from using 89,439 (_merge==2)

matched 82 (_merge==3)
-----------------------------------------

.
. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren if _merge==3

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
 1 40365  67  3 1 1  1
 1 50019 194  9 1 0  1
 2 40126  53  3 1 0  1
 2 40215  68  2 1 0  1
 3 50001 158 12 1 0  1
 3 50009 228 11 1 1  1
 3 50033 236 20 1 2  1
 3 50040 185  1 1 0  1
 4 40087  83  5 1 0  1
 4 50118  60  8 1 0  1
 5 41303  40  2 1 1  1
 6 40114  78  2 2 0  1
 6 40149  99  1 1 0  1
 6 40210  62  1 1 0  2
 6 40380 103  2 2 0  1
 6 60008  96 14 1 0  1
 6 60009  81  7 1 1  1
 7 40041 117  5 1 2  1
 7 40161  15  3 1 1  1
 7 40191  64  1 1 0  1
 7 40256  48  4 3 0  1
 7 50041 301  3 1 1  1
 7 50105 248 13 1 1  1
 7 60011 131 16 1 1  1
 7 60025 207 18 1 0  1
 8 40045  16  1 1 1  1
 8 40303  78  1 1 1  1
 8 50021   4  1 1 0  1
 8 50088   8  4 1 0  6
 8 50090  56  5 1 0  2
10 41190  26  5 1 0  1
11 40399  27  4 1 0  2
11 50050 125 10 1 1  1
11 60001 215 16 1 0  1
12 40179  70  4 1 0  2
12 40186  61  4 2 0  1
12 50028 184  4 1 0  3
13 60011 160 18 1 2  1
14 40172 183  5 1 0  1
14 50019 132 12 1 2  1
14 50033 133 11 3 1  1
15 41270  11  4 1 1  1
16 40175 121  2 1 0  1
16 50017 205 13 1 1  3
16 60026 258 11 1 0  1
17 50019  51  4 1 0  1
17 60006 215 11 1 1  1
17 60014  79 10 1 0  1
18 40023  42  2 1 1  1
18 40136  37  2 1 0  1
18 40216  68  3 1 0  1
18 60007 138 10 1 0  1
18 60010 103  5 1 1  1
19 50011 152 18 1 1  1
19 50022 157  2 1 0  1
19 60012 159 18 1 1  1
20 40114  54  5 1 2  1
20 40313  83  3 1 0  1
21 40210 263  5 1 1  1
23 50112 201 15 1 0  4
24 40058  23  1 1 0  1
24 40122  81  5 1 0  1
24 40128  97  4 1 1  1
24 40174  18  1 1 0 11
24 40185 133  5 1 0  6
24 40377 108  5 1 0  2
24 60014 169 13 1 0  3
25 50016 219  3 1 0  1
26 50102  26  2 1 2  1
26 60010 205 20 1 0  3
27 40318  52  4 1 0  1
27 50022  62  7 1 0  1
27 60006 229 12 2 0  1
28 50091 113  4 1 1  1
28 50253  21  2 1 0  1
29 40011  55  1 1 0  1
29 40108  53  5 1 0  1
32 40242 104  2 1 1  1
32 40321 167  5 1 0  1
32 50011  45  2 1 0  1
32 50060 112 12 1 0  2
32 50060 253 19 1 0  1
end

------------------ copy up to and including the previous line ------------------

Listed 82 out of 141620 observations

. use "/Users/Abelardo/OneDrive/A ARL papers/ST wk/mti2017_hog.dta", clear

.
. merge m:1 ent con n_pro_viv v_sel n_hog h_mud n_ren using ///
> "/Users/Abelardo/OneDrive/A ARL papers/ST wk/mti2017_sdem.dta"

Result # of obs.
-----------------------------------------
not matched 141,538
from master 52,099 (_merge==1)
from using 89,439 (_merge==2)

matched 82 (_merge==3)
-----------------------------------------

. dataex ent con n_pro_viv v_sel n_hog h_mud n_ren if _merge==3

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ent long con int n_pro_viv byte(v_sel n_hog h_mud n_ren)
 1 40365  67  3 1 1  1
 1 50019 194  9 1 0  1
 2 40126  53  3 1 0  1
 2 40215  68  2 1 0  1
 3 50001 158 12 1 0  1
 3 50009 228 11 1 1  1
 3 50033 236 20 1 2  1
 3 50040 185  1 1 0  1
 4 40087  83  5 1 0  1
 4 50118  60  8 1 0  1
 5 41303  40  2 1 1  1
 6 40114  78  2 2 0  1
 6 40149  99  1 1 0  1
 6 40210  62  1 1 0  2
 6 40380 103  2 2 0  1
 6 60008  96 14 1 0  1
 6 60009  81  7 1 1  1
 7 40041 117  5 1 2  1
 7 40161  15  3 1 1  1
 7 40191  64  1 1 0  1
 7 40256  48  4 3 0  1
 7 50041 301  3 1 1  1
 7 50105 248 13 1 1  1
 7 60011 131 16 1 1  1
 7 60025 207 18 1 0  1
 8 40045  16  1 1 1  1
 8 40303  78  1 1 1  1
 8 50021   4  1 1 0  1
 8 50088   8  4 1 0  6
 8 50090  56  5 1 0  2
10 41190  26  5 1 0  1
11 40399  27  4 1 0  2
11 50050 125 10 1 1  1
11 60001 215 16 1 0  1
12 40179  70  4 1 0  2
12 40186  61  4 2 0  1
12 50028 184  4 1 0  3
13 60011 160 18 1 2  1
14 40172 183  5 1 0  1
14 50019 132 12 1 2  1
14 50033 133 11 3 1  1
15 41270  11  4 1 1  1
16 40175 121  2 1 0  1
16 50017 205 13 1 1  3
16 60026 258 11 1 0  1
17 50019  51  4 1 0  1
17 60006 215 11 1 1  1
17 60014  79 10 1 0  1
18 40023  42  2 1 1  1
18 40136  37  2 1 0  1
18 40216  68  3 1 0  1
18 60007 138 10 1 0  1
18 60010 103  5 1 1  1
19 50011 152 18 1 1  1
19 50022 157  2 1 0  1
19 60012 159 18 1 1  1
20 40114  54  5 1 2  1
20 40313  83  3 1 0  1
21 40210 263  5 1 1  1
23 50112 201 15 1 0  4
24 40058  23  1 1 0  1
24 40122  81  5 1 0  1
24 40128  97  4 1 1  1
24 40174  18  1 1 0 11
24 40185 133  5 1 0  6
24 40377 108  5 1 0  2
24 60014 169 13 1 0  3
25 50016 219  3 1 0  1
26 50102  26  2 1 2  1
26 60010 205 20 1 0  3
27 40318  52  4 1 0  1
27 50022  62  7 1 0  1
27 60006 229 12 2 0  1
28 50091 113  4 1 1  1
28 50253  21  2 1 0  1
29 40011  55  1 1 0  1
29 40108  53  5 1 0  1
32 40242 104  2 1 1  1
32 40321 167  5 1 0  1
32 50011  45  2 1 0  1
32 50060 112 12 1 0  2
32 50060 253 19 1 0  1
end

------------------ copy up to and including the previous line ------------------

Listed 82 out of 141620 observations

.
.
end of do-file

.

[William Lisowski]

I think your understanding of the data is incomplete.

From the sorted examples in post #5, I took the observations with con <= 40025 and merged them. Here are the results.

 +---------------------------------------------------------------------------+
  | ent     con   n_pro_viv   v_sel   n_hog   h_mud   n_ren            _merge |
  |---------------------------------------------------------------------------|
  |   1   40001          26       5       1       0       3   master only (1) |
  |   1   40001          26       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40001          85       4       1       0       3   master only (1) |
  |   1   40001          85       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40001          97       1       1       0       4   master only (1) |
  |   1   40001          97       1       1       0       3   master only (1) |
  |   1   40001          97       1       1       0       5   master only (1) |
  |   1   40001          97       1       1       0       6   master only (1) |
  |   1   40001          97       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40002          77       4       1       0       4   master only (1) |
  |   1   40002          77       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40002          98       5       1       0       6   master only (1) |
  |   1   40002          98       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40003          10       1       1       0       3   master only (1) |
  |   1   40003          10       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40003          58       5       1       0       3   master only (1) |
  |   1   40003          58       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40003          98       3       1       0       4   master only (1) |
  |   1   40003          98       3       1       0       3   master only (1) |
  |   1   40003          98       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40004          43       5       1       0       3   master only (1) |
  |   1   40004          43       5       1       0       4   master only (1) |
  |   1   40004          43       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40004          65       1       1       0       4   master only (1) |
  |   1   40004          65       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40004         117       3       1       0       4   master only (1) |
  |   1   40004         117       3       1       0       5   master only (1) |
  |   1   40004         117       3       1       0       6   master only (1) |
  |   1   40004         117       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40005           8       1       1       0       4   master only (1) |
  |   1   40005           8       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40005          64       3       1       0       5   master only (1) |
  |   1   40005          64       3       1       0       4   master only (1) |
  |   1   40005          64       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40006          13       5       1       0       4   master only (1) |
  |   1   40006          13       5       1       0       3   master only (1) |
  |   1   40006          13       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40006          71       2       1       0       5   master only (1) |
  |   1   40006          71       2       1       0       4   master only (1) |
  |   1   40006          71       2       1       0       3   master only (1) |
  |   1   40006          71       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40006          88       3       1       0       5   master only (1) |
  |   1   40006          88       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40007           6       4       1       0       3   master only (1) |
  |   1   40007           6       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40007          36       5       1       0       4   master only (1) |
  |   1   40007          36       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40007          59       1       1       0       5   master only (1) |
  |   1   40007          59       1       1       0       4   master only (1) |
  |   1   40007          59       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40010          40       5       1       0       5   master only (1) |
  |   1   40010          40       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40010          58       2       1       0       3   master only (1) |
  |   1   40010          58       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40011          22       1       1       0       4   master only (1) |
  |   1   40011          22       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40011          81       4       1       0       3   master only (1) |
  |   1   40011          81       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40011         110       5       1       0       5   master only (1) |
  |   1   40011         110       5       1       0       4   master only (1) |
  |   1   40011         110       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40012          38       3       1       0       5   master only (1) |
  |   1   40012          38       3       1       0       4   master only (1) |
  |   1   40012          38       3       1       0       6   master only (1) |
  |   1   40012          38       3       1       0       7   master only (1) |
  |   1   40012          38       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40015          24       1       1       0       6   master only (1) |
  |   1   40015          24       1       1       0       4   master only (1) |
  |   1   40015          24       1       1       0       5   master only (1) |
  |   1   40015          24       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40015          87       5       1       0       3   master only (1) |
  |   1   40015          87       5       1       0       9   master only (1) |
  |   1   40015          87       5       1       0       5   master only (1) |
  |   1   40015          87       5       1       0       4   master only (1) |
  |   1   40015          87       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40015          90       4       1       0       3   master only (1) |
  |   1   40015          90       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40016          43       4       1       0       4   master only (1) |
  |   1   40016          43       4       1       0       3   master only (1) |
  |   1   40016          43       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40016          58       2       1       0       4   master only (1) |
  |   1   40016          58       2       1       0       3   master only (1) |
  |   1   40016          58       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40016          82       3       1       0       4   master only (1) |
  |   1   40016          82       3       1       0       3   master only (1) |
  |   1   40016          82       3       1       0       5   master only (1) |
  |   1   40016          82       3       1       0       2    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40016          84       1       1       0       3   master only (1) |
  |   1   40016          84       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40017          23       3       1       0       7   master only (1) |
  |   1   40017          23       3       1       0       4   master only (1) |
  |   1   40017          23       3       1       0       3   master only (1) |
  |   1   40017          23       3       1       0       6   master only (1) |
  |   1   40017          23       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40017         122       1       1       0       3   master only (1) |
  |   1   40017         122       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40017         154       2       1       0       3   master only (1) |
  |   1   40017         154       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40018          98       2       1       0       5   master only (1) |
  |   1   40018          98       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40018         126       3       1       0       5   master only (1) |
  |   1   40018         126       3       1       0       4   master only (1) |
  |   1   40018         126       3       1       0       3   master only (1) |
  |   1   40018         126       3       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40019          13       1       1       0       5   master only (1) |
  |   1   40019          13       1       1       0       3   master only (1) |
  |   1   40019          13       1       1       0       6   master only (1) |
  |   1   40019          13       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40019          81       4       1       0       4   master only (1) |
  |   1   40019          81       4       1       0       6   master only (1) |
  |   1   40019          81       4       1       0       5   master only (1) |
  |   1   40019          81       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40019          97       5       1       0       3   master only (1) |
  |   1   40019          97       5       1       0       4   master only (1) |
  |   1   40019          97       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40020          14       2       1       0       6   master only (1) |
  |   1   40020          14       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40020          51       1       1       0       3   master only (1) |
  |   1   40020          51       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40021           4       1       1       0       4   master only (1) |
  |   1   40021           4       1       1       0       3   master only (1) |
  |   1   40021           4       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40021          36       2       1       0       4   master only (1) |
  |   1   40021          36       2       1       0       6   master only (1) |
  |   1   40021          36       2       1       0       5   master only (1) |
  |   1   40021          36       2       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40021         100       4       1       0       2   master only (1) |
  |   1   40021         100       4       1       0       3   master only (1) |
  |   1   40021         100       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40021         127       5       1       0       2   master only (1) |
  |   1   40021         127       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40022          62       5       1       0       2   master only (1) |
  |   1   40022          62       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40022         117       3       1       0       5   master only (1) |
  |   1   40022         117       3       1       0       2    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40022         122       4       1       0       4   master only (1) |
  |   1   40022         122       4       1       0       3   master only (1) |
  |   1   40022         122       4       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40023          19       1       1       0       6   master only (1) |
  |   1   40023          19       1       1       0       4   master only (1) |
  |   1   40023          19       1       1       0       5   master only (1) |
  |   1   40023          19       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40025           4       5       1       0       3   master only (1) |
  |   1   40025           4       5       1       0       4   master only (1) |
  |   1   40025           4       5       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40025          13       1       1       0       6   master only (1) |
  |   1   40025          13       1       1       0       5   master only (1) |
  |   1   40025          13       1       1       0       2   master only (1) |
  |   1   40025          13       1       1       0       1    using only (2) |
  |---------------------------------------------------------------------------|
  |   1   40025          52       3       1       0       4   master only (1) |
  |   1   40025          52       3       1       0       3   master only (1) |
  |   1   40025          52       3       1       0       2    using only (2) |
  +---------------------------------------------------------------------------+

As you can see, nothing matched, and the differences are entirely because the seventh variable n_ren is much different in the two datasets.

I think perhaps your understanding that

there are seven key variables required to successfully merge two data sets

ia inaccurate and only the first six are required, or else your understanding of the seventh variable is incorrect. Since I'm not familiar with your data, that's about as much as I can conclude. I think your next step is to revisit the documentation and see if you misunderstood, perhaps talking to someone who has used the data, or contacting the source to see if they can advise you further.

[Abelardo Rodriguez]

Thanks! Only six key variables are needed for a 100% compatibility using 1:m merge. Will check email correspondence from the official on-line help.