Problem with Merge

[fai ot]

Hi every one,
I am trying to merge two data set and the id variable is country,
when I merge the data sets I didn't get a combined data sets but I get a data with the two lists of country and the new data shows me missing values for the countries of data1 and values for the countries of data2.
PS: the country varaible contains the names of countries that are similar for both data sets.
Thank you
F.

[William Lisowski]

Your question really isn't clear without more detail, or at a minimum it is too difficult to guess at a good answer from what you have shared. Please help us help you. Show example data from each of your two datasets. Show your code. Show us what the merge command told you. Tell us what precisely is wrong. The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output.

[fai ot]

Thank you for your repsonse and Sorry if it wasn't clear,
the common variable (data 1 and 2) country contains the list of countries, the two data should merge based on this common variable.
the list of country of data 1 is :

. list countryname

+------------------------+
| countryname |
|------------------------|
1. | Afghanistan |
2. | Albania |
3. | Algeria |
4. | Angola |
5. | Argentina |
|------------------------|
6. | Armenia |
7. | Azerbaijan |
8. | Bahamas, The |
9. | Bahrain |
10. | Bangladesh |
|------------------------|
11. | Barbados |
12. | Belarus |
13. | Belize |
14. | Benin |
15. | Bhutan |
|------------------------|
16. | Bolivia |
17. | Bosnia and Herzegovina |
18. | Botswana |
19. | Brazil |
20. | Brunei Darussalam |
|------------------------|
21. | Bulgaria |
22. | Burkina Faso |
23. | Burundi |
24. | Cabo Verde |
25. | Cambodia |
|------------------------|
26. | Cameroon |
27. | Chad |
28. | Chile |
29. | China |
30. | Colombia |
|------------------------|
31. | Congo, Dem. Rep. |
32. | Costa Rica |
33. | Cote d'Ivoire |
34. | Croatia |
35. | Cuba |
|------------------------|
36. | Cyprus |
37. | Czech Republic |
38. | Dominican Republic |
39. | Ecuador |
40. | Egypt, Arab Rep. |
|------------------------|
41. | El Salvador |
42. | Estonia |
43. | Ethiopia |
44. | Fiji |
45. | Gambia, The |
|------------------------|
46. | Georgia |
47. | Ghana |
48. | Greece |
49. | Guatemala |
50. | Guinea |
|------------------------|
51. | Guyana |
52. | Honduras |
53. | Hungary |
54. | India |
55. | Indonesia |
|------------------------|
56. | Iran, Islamic Rep. |
57. | Iraq |
58. | Jamaica |
59. | Jordan |
60. | Kazakhstan |
|------------------------|
61. | Kenya |
62. | Korea, Rep. |
63. | Kuwait |
64. | Kyrgyz Republic |
65. | Lao PDR |
|------------------------|
66. | Latvia |
67. | Lebanon |
68. | Lesotho |
69. | Liberia |
70. | Lithuania |
|------------------------|
71. | Macedonia, FYR |
72. | Madagascar |
73. | Malawi |
74. | Malaysia |
75. | Maldives |
|------------------------|
76. | Mali |
77. | Malta |
78. | Mauritania |
79. | Mauritius |
80. | Mexico |
|------------------------|
81. | Moldova |
82. | Mongolia |
83. | Montenegro |
84. | Morocco |
85. | Mozambique |
|------------------------|
86. | Myanmar |
87. | Namibia |
88. | Nepal |
89. | Nicaragua |
90. | Niger |
|------------------------|
91. | Nigeria |
92. | Oman |
93. | Pakistan |
94. | Panama |
95. | Papua New Guinea |
|------------------------|
96. | Paraguay |
97. | Peru |
98. | Philippines |
99. | Poland |
100. | Portugal |
|------------------------|
101. | Qatar |
102. | Romania |
103. | Russian Federation |
104. | Rwanda |
105. | Senegal |
|------------------------|
106. | Serbia |
107. | Sierra Leone |
108. | Singapore |
109. | Slovak Republic |
110. | Slovenia |
|------------------------|
111. | South Africa |
112. | Sri Lanka |
113. | Suriname |
114. | Swaziland |
115. | Syrian Arab Republic |
|------------------------|
116. | Tajikistan |
117. | Tanzania |
118. | Thailand |
119. | Timor-Leste |
120. | Togo |
|------------------------|
121. | Trinidad and Tobago |
122. | Tunisia |
123. | Turkey |
124. | Uganda |
125. | Ukraine |
|------------------------|
126. | Uruguay |
127. | Uzbekistan |
128. | Vanuatu |
129. | Venezuela, RB |
130. | Vietnam |
|------------------------|
131. | Yemen, Rep. |
132. | Zambia |
133. | Zimbabwe |
+------------------------+




The list of countryname data 2 is :


+---------------------------------------------+
| countryname |
|---------------------------------------------|
1. | Angola |
2. | Anguilla |
3. | Antigua and Barbuda |
4. | Barbados |
5. | Belize |
|---------------------------------------------|
6. | Benin |
7. | Botswana |
8. | Burkina Faso |
9. | Burundi |
10. | Cabo Verde |
|---------------------------------------------|
11. | Cameroon |
12. | Central African Republic |
13. | Chad |
14. | Comoros |
15. | Congo |
|---------------------------------------------|
16. | Costa Rica |
17. | Cuba |
18. | Côte d'Ivoire |
19. | Democratic Republic of the Congo |
20. | Djibouti |
|---------------------------------------------|
21. | Dominica |
22. | Dominican Republic |
23. | El Salvador |
24. | Equatorial Guinea |
25. | Eritrea |
|---------------------------------------------|
26. | Eswatini |
27. | Ethiopia |
28. | Gabon |
29. | Gambia |
30. | Ghana |
|---------------------------------------------|
31. | Grenada |
32. | Guatemala |
33. | Guinea |
34. | Guinea-Bissau |
35. | Haiti |
|---------------------------------------------|
36. | Honduras |
37. | Jamaica |
38. | Kenya |
39. | Lesotho |
40. | Liberia |
|---------------------------------------------|
41. | Madagascar |
42. | Malawi |
43. | Mali |
44. | Mauritania |
45. | Mauritius |
|---------------------------------------------|
46. | Mayotte |
47. | Mexico |
48. | Montserrat |
49. | Mozambique |
50. | Namibia |
|---------------------------------------------|
51. | Nicaragua |
52. | Niger |
53. | Nigeria |
54. | Panama |
55. | Rwanda |
|---------------------------------------------|
56. | Saint Helena |
57. | Saint Kitts and Nevis |
58. | Saint Lucia |
59. | Saint Vincent and the Grenadines |
60. | Sao Tome and Principe |
|---------------------------------------------|
61. | Senegal |
62. | Seychelles |
63. | Sierra Leone |
64. | Somalia |
65. | South Africa |
|---------------------------------------------|
66. | South Sudan |
67. | Sudan |
68. | Tanzania |
69. | Togo |
70. | Trinidad and Tobago |
|---------------------------------------------|
71. | Uganda |
72. | Zambia |
73. | Zimbabwe |
74. | Afghanistan |
75. | Algeria |
|---------------------------------------------|
76. | Argentina |
77. | Armenia |
78. | Azerbaijan |
79. | Bangladesh |
80. | Bhutan |
|---------------------------------------------|
81. | Bolivia |
82. | Brazil |
83. | Cambodia |
84. | Chile |
85. | China (People's Republic of) |
|---------------------------------------------|
86. | Colombia |
87. | Cook Islands |
88. | Democratic People's Republic of Korea |
89. | Ecuador |
90. | Egypt |
|---------------------------------------------|
91. | Fiji |
92. | Georgia |
93. | Guyana |
94. | India |
95. | Indonesia |
|---------------------------------------------|
96. | Iran |
97. | Iraq |
98. | Jordan |
99. | Kazakhstan |
100. | Kiribati |
|---------------------------------------------|
101. | Kyrgyzstan |
102. | Lao People's Democratic Republic |
103. | Lebanon |
104. | Libya |
105. | Malaysia |
|---------------------------------------------|
106. | Maldives |
107. | Marshall Islands |
108. | Micronesia |
109. | Mongolia |
110. | Morocco |
|---------------------------------------------|
111. | Myanmar |
112. | Nauru |
113. | Nepal |
114. | Niue |
115. | North of Sahara, regional |
|---------------------------------------------|
116. | Oman |
117. | Pakistan |
118. | Palau |
119. | Papua New Guinea |
120. | Paraguay |
|---------------------------------------------|
121. | Peru |
122. | Philippines |
123. | Samoa |
124. | Solomon Islands |
125. | Sri Lanka |
|---------------------------------------------|
126. | Suriname |
127. | Syrian Arab Republic |
128. | Tajikistan |
129. | Thailand |
130. | Timor-Leste |
|---------------------------------------------|
131. | Tokelau |
132. | Tonga |
133. | Tunisia |
134. | Turkmenistan |
135. | Tuvalu |
|---------------------------------------------|
136. | Uruguay |
137. | Uzbekistan |
138. | Vanuatu |
139. | Venezuela |
140. | Viet Nam |
|---------------------------------------------|
141. | Wallis and Futuna |
142. | West Bank and Gaza Strip |
143. | Yemen |
144. | Albania |
145. | Belarus |
|---------------------------------------------|
146. | Bosnia and Herzegovina |
147. | Croatia |
148. | Kosovo |
149. | Moldova |
150. | Montenegro |
|---------------------------------------------|
151. | Serbia |
152. | Turkey |
153. | Ukraine |
+---------------------------------------------+



The question is: when I merge by using merge 1:1 countryname using "D:\PC Gap Index.dta", noge
> nerate

Result # of obs.
-----------------------------------------
not matched 286
from master 153
from using 133

matched 0
-----------------------------------------

I didn't get any data merged , the problem is a lot of countries are commonly listed both in data 1 and data2 but the command didn't match any of them together.
and I don't know where is the problem.

Thanks
F.

[William Lisowski]

I am going to guess that in one or both of your datasets, your country names have spaces before the first letter or after the final letter, since absolutely nothing matched.

I recommend trying

use dataset2
replace countryname = trim(countryname)
save trimdataset2
use dataset1
replace countryname = trim(countryname)
merge 1:1 countryname using trimdataset2

[Clyde Schechter]

I'll reiterate a part of what William Lisowski advised in #2:

The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output.

What you have shown is the output of -list- from both data sets. But -list- output does not show the data the way Stata sees it. That's why one of many reasons why the Forum FAQ advises using the -dataex- command to post example data--it shows the data as Stata sees it. My best guess is that the variable countryname is, in each data set, a value-labeled numeric variable, not a string. And for whatever reason, when the data sets were created, the numerical values corresponding to each country name were different, so nothing matches numerically when you compose them. This is, I think, the most likely cause of your difficulty--but without seeing the data the way Stata sees it, I cannot be sure.

If I am right about this, what you will have to do is

decode countryname, gen(str_countryname)
drop countryname
rename str_countryname countryname
replace countryname = trim(itrim(countryname))

in each of the data sets, and save the resulting data sets. Then you can -merge- those two data sets together: both data sets will have countryname as a string variable, and the strings will match.

If you are running version 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. Always use -dataex- to show example data.

Added: Crossed with #4, which suggests essentially the same solution. Also, in future work, I strongly recommend that before you -merge- or -append- any data sets, you run the -precombine- program (by Mark Chatfield, and available from Stata Journal). It will warn you in advance of this and other potential difficulties presented by differences in data representation in the data sets you are working with.

[fai ot]

Dear William ,
Thank you for the solution, it works very well .