merging different levels of data

[Ethan Gaala]

I have three datasets that I want to merge for multilevel regression. The first one is individual-level survey data (afro6new). The other two are country-level datasets. When I try to merge, I'm told my 'country' and 'year' variables do not uniquely identify data. I have tried several reshaping techniques but only errors, and I could not figure how to reshape it. Added, to that, some of my data are in string and I can't quite destring them all with this command:

foreach var of varlist country countrycode year timecode gdppercapitacurrentusnygdppcapcd gdppercapitapppcurrentinternatio gdppppcurrentinternationalnygdpm cpiatransparencyaccountabilityan cpiapoliciesforsocialinclusioneq cpiaqualityofbudgetaryandfinanci cpiafinancialsectorrating1lowto6 unemploymenttotaloftotallaborfor v13 {
destring `var', replace
}

code and error
use afro6new.dta
. merge 1:1 country year using p4v2018trimmed.dta
variables country year do not uniquely identify observations in the master data
r(459);


Code and error when i try using the other dataset as the master
use wdi.dta
merge 1:1 country year using afro6new.dta
key variable country is str48 in master but byte in using data
Each key variable -- the variables on which observations are matched -- must be of the
same generic type in the master and using datasets. Same generic type means both numeric
or both string.



Please help.

First dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte country int year str7 RESPNO byte Q27E
1 2015 "ALG0001" 9
1 2015 "ALG0002" 0
1 2015 "ALG0003" 0
1 2015 "ALG0004" 0
1 2015 "ALG0005" 0
1 2015 "ALG0006" 9
1 2015 "ALG0007" 3
1 2015 "ALG0008" 1
1 2015 "ALG0009" 0
1 2015 "ALG0010" 0
1 2015 "ALG0011" 2
1 2015 "ALG0012" 0
1 2015 "ALG0013" 0
1 2015 "ALG0014" 1
1 2015 "ALG0015" 0
1 2015 "ALG0016" 1
1 2015 "ALG0017" 1
1 2015 "ALG0018" 9
1 2015 "ALG0019" 0
1 2015 "ALG0020" 0
1 2015 "ALG0021" 4
1 2015 "ALG0022" 0
1 2015 "ALG0023" 0
1 2015 "ALG0024" 0
1 2015 "ALG0025" 0
1 2015 "ALG0026" 0
1 2015 "ALG0027" 2
1 2015 "ALG0028" 0
1 2015 "ALG0029" 0
1 2015 "ALG0030" 0
1 2015 "ALG0031" 0
1 2015 "ALG0032" 0
1 2015 "ALG0033" 0
1 2015 "ALG0034" 0
1 2015 "ALG0035" 0
1 2015 "ALG0036" 0
1 2015 "ALG0037" 0
1 2015 "ALG0038" 0
1 2015 "ALG0039" 0
1 2015 "ALG0040" 0
1 2015 "ALG0041" 1
1 2015 "ALG0042" 1
1 2015 "ALG0043" 0
1 2015 "ALG0044" 0
1 2015 "ALG0045" 0
1 2015 "ALG0046" 0
1 2015 "ALG0047" 0
1 2015 "ALG0048" 0
1 2015 "ALG0049" 1
1 2015 "ALG0050" 1
1 2015 "ALG0051" 2
1 2015 "ALG0052" 0
1 2015 "ALG0053" 1
1 2015 "ALG0054" 1
1 2015 "ALG0055" 0
1 2015 "ALG0056" 1
1 2015 "ALG0057" 0
1 2015 "ALG0058" 0
1 2015 "ALG0059" 2
1 2015 "ALG0060" 0
1 2015 "ALG0061" 1
1 2015 "ALG0062" 0
1 2015 "ALG0063" 0
1 2015 "ALG0064" 0
1 2015 "ALG0065" 1
1 2015 "ALG0066" 0
1 2015 "ALG0067" 1
1 2015 "ALG0068" 1
1 2015 "ALG0069" 1
1 2015 "ALG0070" 1
1 2015 "ALG0071" 0
1 2015 "ALG0072" 0
1 2015 "ALG0073" 1
1 2015 "ALG0074" 0
1 2015 "ALG0075" 1
1 2015 "ALG0076" 0
1 2015 "ALG0077" 0
1 2015 "ALG0078" 0
1 2015 "ALG0079" 0
1 2015 "ALG0080" 0
1 2015 "ALG0081" 0
1 2015 "ALG0082" 0
1 2015 "ALG0083" 0
1 2015 "ALG0084" 0
1 2015 "ALG0085" 0
1 2015 "ALG0086" 0
1 2015 "ALG0087" 1
1 2015 "ALG0088" 0
1 2015 "ALG0089" 0
1 2015 "ALG0090" 0
1 2015 "ALG0091" 1
1 2015 "ALG0092" 0
1 2015 "ALG0093" 1
1 2015 "ALG0094" 0
1 2015 "ALG0095" 9
1 2015 "ALG0096" 0
1 2015 "ALG0097" 1
1 2015 "ALG0098" 0
1 2015 "ALG0099" 0
1 2015 "ALG0100" 1
end
label values country COUNTRY
label def COUNTRY 1 "Algeria", modify
label values Q27E Q27E
label def Q27E 0 "No, would never do this", modify
label def Q27E 1 "No, but would do if had the chance", modify
label def Q27E 2 "Yes, once or twice", modify
label def Q27E 3 "Yes, several times", modify
label def Q27E 4 "Yes, often", modify
label def Q27E 9 "Don't know", modify

Second dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input str48 country int year str16(gdppercapitacurrentusnygdppcapcd gdppercapitapppcurrentinternatio)
"Algeria" 1960 "246.30876252962"  ".."              
"Algeria" 1961 "214.776273516192" ".."              
"Algeria" 1962 "172.245997766356" ".."              
"Algeria" 1963 "226.899988804342" ".."              
"Algeria" 1964 "238.048532020905" ".."              
"Algeria" 1965 "249.883486058815" ".."              
"Algeria" 1966 "235.598111822427" ".."              
"Algeria" 1967 "253.923650753479" ".."              
"Algeria" 1968 "281.925745024389" ".."              
"Algeria" 1969 "302.752306734104" ".."              
"Algeria" 1970 "336.224855584547" ".."              
"Algeria" 1971 "341.388987340498" ".."              
"Algeria" 1972 "442.351878193504" ".."              
"Algeria" 1973 "554.755124929209" ".."              
"Algeria" 1974 "817.988308478181" ".."              
"Algeria" 1975 "936.790025769149" ".."              
"Algeria" 1976 "1037.60703844052" ".."              
"Algeria" 1977 "1192.74388765763" ".."              
"Algeria" 1978 "1456.41939597215" ".."              
"Algeria" 1979 "1782.69798137911" ".."              
"Algeria" 1980 "2202.99736479746" ".."              
"Algeria" 1981 "2237.08632490354" ".."              
"Algeria" 1982 "2210.30192760168" ".."              
"Algeria" 1983 "2312.65561948251" ".."              
"Algeria" 1984 "2467.34642199031" ".."              
"Algeria" 1985 "2582.87958916856" ".."              
"Algeria" 1986 "2757.13052415538" ".."              
"Algeria" 1987 "2807.33029744222" ".."              
"Algeria" 1988 "2417.3766834056"  ".."              
"Algeria" 1989 "2215.84755797478" ".."              
"Algeria" 1990 "2408.68881482248" "6683.94251044679"
"Algeria" 1991 "1731.61127444317" "6661.13959247123"
"Algeria" 1992 "1776.03667438071" "6774.46596213675"
"Algeria" 1993 "1807.32854846258" "6640.1668105834" 
"Algeria" 1994 "1507.86531803627" "6583.17260367457"
"Algeria" 1995 "1452.26944487837" "6844.63963324371"
"Algeria" 1996 "1603.93792062434" "7129.63842232678"
"Algeria" 1997 "1619.80622391421" "7214.87270695396"
"Algeria" 1998 "1596.00371429844" "7553.94658827198"
"Algeria" 1999 "1588.2984525177"  "7797.01507100731"
"Algeria" 2000 "1764.88822213372" "8162.58757272877"
"Algeria" 2001 "1740.56006870277" "8480.07045862817"
"Algeria" 2002 "1781.75857132265" "8981.34926734444"
"Algeria" 2003 "2103.4531050538"  "9682.4760351135" 
"Algeria" 2004 "2609.94560777437" "10234.9309046785"
"Algeria" 2005 "3113.10109432814" "11022.1474669711"
"Algeria" 2006 "3478.81854327462" "11380.0944227252"
"Algeria" 2007 "3950.56160744508" "11897.1038212039"
"Algeria" 2008 "4923.54009872171" "12218.0473865624"
"Algeria" 2009 "3883.37814284703" "12294.6124889925"
"Algeria" 2010 "4480.72453900113" "12655.1374103049"
"Algeria" 2011 "5455.74133764262" "13046.1285113709"
"Algeria" 2012 "5592.32609806051" "13482.7211230765"
"Algeria" 2013 "5499.58148704572" "13823.8282325269"
"Algeria" 2014 "5493.02558996263" "14326.283007894" 
"Algeria" 2015 "4177.86751715913" "14711.2208543312"
"Algeria" 2016 "3946.4214445883"  "15036.3641495916"
"Algeria" 2017 "4044.29837226523" "15207.1791488773"
"Algeria" 2018 "4114.71506136896" "15481.7876195754"
"Algeria" 2019 ".."               ".."              
"Benin"   1960 "93.0225089907107" ".."              
"Benin"   1961 "95.5721547147448" ".."              
"Benin"   1962 "94.4645349843821" ".."              
"Benin"   1963 "99.8591138855553" ".."              
"Benin"   1964 "104.339768039886" ".."              
"Benin"   1965 "110.132793835113" ".."              
"Benin"   1966 "112.940836383512" ".."              
"Benin"   1967 "111.951601925238" ".."              
"Benin"   1968 "116.89506602186"  ".."              
"Benin"   1969 "116.025094341185" ".."              
"Benin"   1970 "114.556596466987" ".."              
"Benin"   1971 "112.570089087637" ".."              
"Benin"   1972 "134.819407935009" ".."              
"Benin"   1973 "161.987373671434" ".."              
"Benin"   1974 "174.014149085561" ".."              
"Benin"   1975 "207.300439745473" ".."              
"Benin"   1976 "208.656153830514" ".."              
"Benin"   1977 "218.4543657692"   ".."              
"Benin"   1978 "263.581057550946" ".."              
"Benin"   1979 "327.821677993792" ".."              
"Benin"   1980 "378.043898303902" ".."              
"Benin"   1981 "337.978194739187" ".."              
"Benin"   1982 "322.77769945337"  ".."              
"Benin"   1983 "271.129240225224" ".."              
"Benin"   1984 "252.869785044788" ".."              
"Benin"   1985 "244.410998858469" ".."              
"Benin"   1986 "303.348897858296" ".."              
"Benin"   1987 "344.503070791789" ".."              
"Benin"   1988 "346.736037681422" ".."              
"Benin"   1989 "311.678303860822" ".."              
"Benin"   1990 "393.686214423531" "949.168690914722"
"Benin"   1991 "385.753616012638" "988.77101457638" 
"Benin"   1992 "317.962855286116" "1005.61460513474"
"Benin"   1993 "411.926030522283" "1052.03860879311"
"Benin"   1994 "279.666504326806" "1059.29204604852"
"Benin"   1995 "367.387341032599" "1109.72406044667"
"Benin"   1996 "387.432924636251" "1142.40629405757"
"Benin"   1997 "361.100269794441" "1192.09566510535"
"Benin"   1998 "379.442353954477" "1216.72738664615"
"Benin"   1999 "403.623703839155" "1262.40524394344"
end

Third Dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input str24 country int year byte(polity2 democ)
"Benin"   2013 7 7
"Benin"   2014 7 7
"Benin"   2015 7 7
"Benin"   2016 7 7
"Algeria" 2013 2 3
"Algeria" 2014 2 3
"Algeria" 2015 2 3
"Algeria" 2016 2 3
end

[Clyde Schechter]

You are using -merge- incorrectly. Although country and year do not uniquely identify observations in the first data set (which is obvious on inspecting the data), they do uniquely identify observations in the third data set (or at least in the excerpt of it that you show in #1). So:

use first_data_set, clear
merge m:1 country year using third_dataset

will accomplish what you seek.

Added: If you have still other data sets that you want to combine for this project, first take all the data sets where country and year do uniquely identify observations and merge (1:1) them together with each other. Then merge the result with your individual level data set. as above.

[Ethan Gaala]

Thank you, Clyde. That worked.