Why can´t I get the Data Merge right ?

[Maximilian Kranz]

I already have a data set of FDI inflows and outflows by country and year merged with a restrictiveness index to control for the effect of restrictiveness on FDI flows.
Now I want to add some more information that i get from the CPEII gravity data set.
The FDI inflows and Outflows and the gravity have their own ISO3 that identifies the country's variable already included.
i just want to merge them but i get the variable does not uniquely identify... error all the time



Cpeii Gravity Dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str5(country_id_o country_id_d) str3(iso3_o iso3_d) int dist byte col45
1948 "ABW" "ABW" "ABW" "ABW" . .
1949 "ABW" "ABW" "ABW" "ABW" . .
1950 "ABW" "ABW" "ABW" "ABW" . .
1951 "ABW" "ABW" "ABW" "ABW" . .
1952 "ABW" "ABW" "ABW" "ABW" . .
1953 "ABW" "ABW" "ABW" "ABW" . .
1954 "ABW" "ABW" "ABW" "ABW" . .
1955 "ABW" "ABW" "ABW" "ABW" . .
1956 "ABW" "ABW" "ABW" "ABW" . .
1957 "ABW" "ABW" "ABW" "ABW" . .
1958 "ABW" "ABW" "ABW" "ABW" . .
1959 "ABW" "ABW" "ABW" "ABW" . .
1960 "ABW" "ABW" "ABW" "ABW" . .
1961 "ABW" "ABW" "ABW" "ABW" . .
1962 "ABW" "ABW" "ABW" "ABW" . .
1963 "ABW" "ABW" "ABW" "ABW" . .
1964 "ABW" "ABW" "ABW" "ABW" . .
1965 "ABW" "ABW" "ABW" "ABW" . .
1966 "ABW" "ABW" "ABW" "ABW" . .
1967 "ABW" "ABW" "ABW" "ABW" . .
1968 "ABW" "ABW" "ABW" "ABW" . .
1969 "ABW" "ABW" "ABW" "ABW" . .
1970 "ABW" "ABW" "ABW" "ABW" . .
1971 "ABW" "ABW" "ABW" "ABW" . .
1972 "ABW" "ABW" "ABW" "ABW" . .
1973 "ABW" "ABW" "ABW" "ABW" . .
1974 "ABW" "ABW" "ABW" "ABW" . .
1975 "ABW" "ABW" "ABW" "ABW" . .
1976 "ABW" "ABW" "ABW" "ABW" . .
1977 "ABW" "ABW" "ABW" "ABW" . .
1978 "ABW" "ABW" "ABW" "ABW" . .
1979 "ABW" "ABW" "ABW" "ABW" . .
1980 "ABW" "ABW" "ABW" "ABW" . .
1981 "ABW" "ABW" "ABW" "ABW" . .
1982 "ABW" "ABW" "ABW" "ABW" . .
1983 "ABW" "ABW" "ABW" "ABW" . .
1984 "ABW" "ABW" "ABW" "ABW" . .
1985 "ABW" "ABW" "ABW" "ABW" . .
1986 "ABW" "ABW" "ABW" "ABW" 5 0
1987 "ABW" "ABW" "ABW" "ABW" 5 0
1988 "ABW" "ABW" "ABW" "ABW" 5 0
1989 "ABW" "ABW" "ABW" "ABW" 5 0
1990 "ABW" "ABW" "ABW" "ABW" 5 0
1991 "ABW" "ABW" "ABW" "ABW" 5 0
1992 "ABW" "ABW" "ABW" "ABW" 5 0
1993 "ABW" "ABW" "ABW" "ABW" 5 0
1994 "ABW" "ABW" "ABW" "ABW" 5 0
1995 "ABW" "ABW" "ABW" "ABW" 5 0
1996 "ABW" "ABW" "ABW" "ABW" 5 0
1997 "ABW" "ABW" "ABW" "ABW" 5 0
1998 "ABW" "ABW" "ABW" "ABW" 5 0
1999 "ABW" "ABW" "ABW" "ABW" 5 0
2000 "ABW" "ABW" "ABW" "ABW" 5 0
2001 "ABW" "ABW" "ABW" "ABW" 5 0
2002 "ABW" "ABW" "ABW" "ABW" 5 0
2003 "ABW" "ABW" "ABW" "ABW" 5 0
2004 "ABW" "ABW" "ABW" "ABW" 5 0
2005 "ABW" "ABW" "ABW" "ABW" 5 0
2006 "ABW" "ABW" "ABW" "ABW" 5 0
2007 "ABW" "ABW" "ABW" "ABW" 5 0
2008 "ABW" "ABW" "ABW" "ABW" 5 0
2009 "ABW" "ABW" "ABW" "ABW" 5 0
2010 "ABW" "ABW" "ABW" "ABW" 5 0
2011 "ABW" "ABW" "ABW" "ABW" 5 0
2012 "ABW" "ABW" "ABW" "ABW" 5 0
2013 "ABW" "ABW" "ABW" "ABW" 5 0
2014 "ABW" "ABW" "ABW" "ABW" 5 0
2015 "ABW" "ABW" "ABW" "ABW" 5 0
2016 "ABW" "ABW" "ABW" "ABW" 5 0
2017 "ABW" "ABW" "ABW" "ABW" 5 0
2018 "ABW" "ABW" "ABW" "ABW" 5 0
2019 "ABW" "ABW" "ABW" "ABW" 5 0
2020 "ABW" "ABW" "ABW" "ABW" 5 0
2021 "ABW" "ABW" "ABW" "ABW" 5 0
1948 "ABW" "AFG" "ABW" "AFG" . .
1949 "ABW" "AFG" "ABW" "AFG" . .
1950 "ABW" "AFG" "ABW" "AFG" . .
1951 "ABW" "AFG" "ABW" "AFG" . .
1952 "ABW" "AFG" "ABW" "AFG" . .
1953 "ABW" "AFG" "ABW" "AFG" . .
1954 "ABW" "AFG" "ABW" "AFG" . .
1955 "ABW" "AFG" "ABW" "AFG" . .
1956 "ABW" "AFG" "ABW" "AFG" . .
1957 "ABW" "AFG" "ABW" "AFG" . .
1958 "ABW" "AFG" "ABW" "AFG" . .
1959 "ABW" "AFG" "ABW" "AFG" . .
1960 "ABW" "AFG" "ABW" "AFG" . .
1961 "ABW" "AFG" "ABW" "AFG" . .
1962 "ABW" "AFG" "ABW" "AFG" . .
1963 "ABW" "AFG" "ABW" "AFG" . .
1964 "ABW" "AFG" "ABW" "AFG" . .
1965 "ABW" "AFG" "ABW" "AFG" . .
1966 "ABW" "AFG" "ABW" "AFG" . .
1967 "ABW" "AFG" "ABW" "AFG" . .
1968 "ABW" "AFG" "ABW" "AFG" . .
1969 "ABW" "AFG" "ABW" "AFG" . .
1970 "ABW" "AFG" "ABW" "AFG" . .
1971 "ABW" "AFG" "ABW" "AFG" . .
1972 "ABW" "AFG" "ABW" "AFG" . .
1973 "ABW" "AFG" "ABW" "AFG" . .
end

Merged FDI dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 cou str6 Partnercountry_type int year float(valueinward valueoutward restrictivness)
"" "" 2017 . . .05360465
"" "" 2018 . . .05360465
"" "" 2019 . . .05360465
"" "" 2020 . . .05360465
"" "" 2019 . . .5963488
"" "" 2020 . . .3014651
"" "" 2013 . . .053
"" "" 2014 . . .053
"" "" 2015 . . .053
"" "" 2016 . . .04674419
"" "" 2017 . . .04674419
"" "" 2018 . . .04674419
"" "" 2019 . . .14674419
"" "" 2020 . . .14674419
"" "" 2018 . . .035
"" "" 2019 . . .035
"" "" 2020 . . .035
"" "" 2020 . . .1648372
"AUS" "R4" 2013 3687.7654 2501.93 .14153488
"AUS" "R4" 2014 4156.301 1992.0677 .14086047
"AUS" "R4" 2015 1475.693 2587.723 .15213954
"AUS" "R4" 2016 -4058.574 596.8929 .16218606
"AUS" "R4" 2017 4428.692 875.9292 .16276744
"AUS" "R4" 2018 4248.898 -3070.1426 .1648372
"AUS" "R4" 2019 2992.285 1106.5546 .1648372
"AUS" "AFG" 2013 0 0 .14153488
"AUS" "AFG" 2014 0 0 .14086047
"AUS" "AFG" 2015 0 0 .15213954
"AUS" "AFG" 2016 0 0 .16218606
"AUS" "AFG" 2017 0 0 .16276744
"AUS" "AFG" 2018 0 0 .1648372
"AUS" "AFG" 2019 0 0 .1648372
"AUS" "ALB" 2013 0 0 .14153488
"AUS" "ALB" 2014 0 0 .14086047
"AUS" "ALB" 2015 0 0 .15213954
"AUS" "ALB" 2016 0 0 .16218606
"AUS" "ALB" 2017 0 0 .16276744
"AUS" "ALB" 2018 0 0 .1648372
"AUS" "ALB" 2019 0 0 .1648372
"AUS" "DZA" 2013 0 0 .14153488
"AUS" "DZA" 2014 . 0 .14086047
"AUS" "DZA" 2015 . 0 .15213954
"AUS" "DZA" 2016 . 0 .16218606
"AUS" "DZA" 2017 . 0 .16276744
"AUS" "DZA" 2018 . 0 .1648372
"AUS" "DZA" 2019 0 . .1648372
"AUS" "ASM" 2013 . 2.894635 .14153488
"AUS" "ASM" 2014 . . .14086047
"AUS" "ASM" 2015 . -15.778796 .15213954
"AUS" "ASM" 2016 . . .16218606
"AUS" "ASM" 2017 . . .16276744
"AUS" "ASM" 2018 . . .1648372
"AUS" "ASM" 2019 . . .1648372
"AUS" "AND" 2013 0 0 .14153488
"AUS" "AND" 2014 0 0 .14086047
"AUS" "AND" 2015 0 0 .15213954
"AUS" "AND" 2016 0 0 .16218606
"AUS" "AND" 2017 0 0 .16276744
"AUS" "AND" 2018 0 0 .1648372
"AUS" "AND" 2019 0 0 .1648372
"AUS" "AGO" 2013 . 0 .14153488
"AUS" "AGO" 2014 . . .14086047
"AUS" "AGO" 2015 . 0 .15213954
"AUS" "AGO" 2016 . . .16218606
"AUS" "AGO" 2017 . . .16276744
"AUS" "AGO" 2018 . . .1648372
"AUS" "AGO" 2019 0 . .1648372
"AUS" "AIA" 2013 0 0 .14153488
"AUS" "AIA" 2014 0 0 .14086047
"AUS" "AIA" 2015 0 0 .15213954
"AUS" "AIA" 2016 0 0 .16218606
"AUS" "AIA" 2017 0 0 .16276744
"AUS" "AIA" 2018 0 0 .1648372
"AUS" "AIA" 2019 0 0 .1648372
"AUS" "ATA" 2013 0 0 .14153488
"AUS" "ATA" 2014 0 0 .14086047
"AUS" "ATA" 2015 0 0 .15213954
"AUS" "ATA" 2016 0 0 .16218606
"AUS" "ATA" 2017 0 0 .16276744
"AUS" "ATA" 2018 0 0 .1648372
"AUS" "ATA" 2019 0 0 .1648372
"AUS" "ATG" 2013 0 0 .14153488
"AUS" "ATG" 2014 0 0 .14086047
"AUS" "ATG" 2015 0 0 .15213954
"AUS" "ATG" 2016 0 0 .16218606
"AUS" "ATG" 2017 0 0 .16276744
"AUS" "ATG" 2018 0 0 .1648372
"AUS" "ATG" 2019 0 0 .1648372
"AUS" "ARG" 2013 . . .14153488
"AUS" "ARG" 2014 . . .14086047
"AUS" "ARG" 2015 . -48.08776 .15213954
"AUS" "ARG" 2016 . . .16218606
"AUS" "ARG" 2017 -2.2990267 . .16276744
"AUS" "ARG" 2018 . .7469934 .1648372
"AUS" "ARG" 2019 . . .1648372
"AUS" "ARM" 2013 0 0 .14153488
"AUS" "ARM" 2014 0 0 .14086047
"AUS" "ARM" 2015 0 0 .15213954
"AUS" "ARM" 2016 0 0 .16218606
"AUS" "ARM" 2017 0 0 .16276744
end
[/CODE]

Thank you for your Help Stata Team!
i hope this is all the information needed to understand what im trying to do.

sincerely Max

[William Lisowski]

If you don't show us the merge command(s) you tried, it's difficult to tell you what needs to be changed.

It is especially interesting because your "merged FDI dataset" shows the identifier variables

cou Partnercountry_type year

while your "CPEII gravity dataset" shows the variables

year country_id_o country_id_d iso3_o iso3_d

suggesting that year was the only available common to the two datasets to be matched on.

Perhaps you mean to do something like

use fdi
generate iso3_o = cou
generate iso3_d = Partnercountry_type
merge 1:1 iso3_o iso3_d year using CPEII

[Maximilian Kranz]

Okay so far I tried just renaming cou and partnercountry_type into iso3_o and iso3_d and then merging it with:

merge 1:1 iso3_o iso3_d year using "\\fs01\mkranz$\Dokumente\Bachlor Daensätze\Gravity_V202202.dta" ,generate(end)


after i used your way of generating iso3_o and iso3_d using the same merge command. But i still always get this error:

variables iso3_o iso3_d year do not uniquely identify observations in the master data


Could that be because the Iso3 numbers of the Gravity data set and the FDI flows data might be different and to be sure I should generate them with the kountry command to make sure they are the same ?

[William Lisowski]

The error message is telling you that if you were to do, using your FDI dataset (not Gravity_V202202),

duplicates report iso3_o iso3_d year

the command would tell you that you have some observations with the same values of iso3_o iso3_d and year.

If you expect duplicates, then perhaps

merge m:1 iso3_o iso3_d year using "\\fs01\mkranz$\Dokumente\Bachlor Daensätze\Gravity_V202202.dta" ,generate(end)

is what you want.

If you do not expect duplicates, the output of

help duplicates

teaches you how to use the duplicates command to find these duplicated observations.

duplicates examples iso3_o iso3_d year

is a place to start, having done duplicates report.