Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Merging files

    Hello, I need help merging some survey data.

    I have the following dataset: household-level data (farminghouseholds), mapping data (00_mapping_hh_com.dta), and a couple of community-level datasets, such as 1_Identification_ano.dta, 2_Respondents_ano, 3_c2q6_Electricity_ano, 4_c2c_Market_Access_ano, 5_c2d_input_support_ano. I use the following codes for merging.

    data.use FarmingHouseholds.dta, clear
    merge 1:1 HHID using "00_mapping_hh_com.dta", keep(match) nogen
    order COMID, after(HHID)
    drop if missing(COMID)
    merge m:1 COMID using "1_Identification_ano.dta", keep(master match) nogen

    However, as I continue to merge I receive errors when I try to merge. I am not sure what do clean in the data to make this work.

    merge m:1 COMID using "3_c2q6_Electricity_ano.dta", keep(master match) nogen
    merge m:1 COMID using "4_c2c_Market_Access_ano.dta", keep(master match) nogen
    merge m:1 COMID using "5_c2d_input_support_ano.dta", keep(master match) nogen
    merge m:1 COMID using "6_c2e_conflicts_ano.dta", keep(master match) nogen
    merge m:1 COMID using "7_c4b_Disaster_ano.dta"", keep(master match) nogen

    isid COMID is not unique among these variables. Is there a way around this?

    Thank you

    Nick Cox
    Clyde Schechter
    Tags: None
  • #2
    I'm going to make a guess here. I don't know where this data comes from, but I notice that all of these files have _ano in their names. And in some languages, ano means year. So perhaps in these data sets are identified by the combination of COMID and year? If so, then it may be that they can be merged with -merge 1:1 COMID year using ...-, or perhaps -merge m:1 COMID year using ...-. (Replace year by the actual name in the data set of whatever variable represents year.)

    If that does not resolve your problem, when you post back, show example data from the data sets so that we can try to see what is going on in them. Be sure to use the -dataex- command to do that. If you are running version 16 or later, 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.

    By the way, it is usually not a good idea to address your posts to specific people. Tonight, I happened to check Statalist one more time before going to bed, which I don't regularly do, and I saw your post. (While I can't say for sure what Nick Cox is doing now, it's a fairly safe bet, based on his location's time zone, that he's asleep.) There are many other Forum members who can help you with this problem, and many of them, based on their time zones and routines, will be active on the Forum before I will next be here again. But by addressing your post to me and Nick, you may have discouraged them from responding. This would mean that you would, at best, be delayed in getting a response, and also you may miss getting better advice from somebody who sees something that I have missed. In general, you should only address your post to a specific person in the context of a thread with several people participating, and where you are addressing something said by one of them and you want to make clear whom you are responding to.

    Comment

    • #3
      Yes, I was asleep. Otherwise, complete agreement with Clyde. As Clyde said, just address the forum collectively, not individuals unless your question is very specific to someone's work. Let people make their own decisions on whether they have the expertise, the interest and the time to answer.
      Last edited by Nick Cox; 13 Jul 2025, 04:00.

      Comment

      • #4
        Thanks Clyde and Nick.

        Here is a better description of the data: HHID = Household ID and COMID = Community ID. I have also uploaded the datasets here

        Code:
        use FarmingHouseholds.dta, clear
merge 1:1 HHID using "00_mapping_hh_com.dta", keep(match) nogen
order COMID, after(HHID)

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str7 HHID str10 COMID
"0000001" "COM0023725"
"0000002" "COM0001896"
"0000003" "COM0017866"
"0000004" "COM0016953"
"0000005" "COM0013514"
"0000006" "COM0015591"
"0000007" "COM0009260"
"0000008" "COM0019383"
"0000009" "COM0024513"
"0000010" "COM0022219"
"0000011" "COM0006835"
"0000012" "COM0007215"
"0000013" "COM0008162"
"0000014" "COM0005931"
"0000015" "COM0024866"
"0000016" "COM0006443"
"0000017" "COM0019976"
"0000018" "COM0020718"
"0000019" "COM0020574"
"0000020" "COM0022864"
"0000021" "COM0014013"
"0000022" "COM0017557"
"0000023" "COM0021959"
"0000024" "COM0023074"
"0000025" "COM0025825"
"0000026" "COM0009983"
"0000027" "COM0001879"
"0000028" "COM0001795"
"0000029" "COM0004882"
"0000030" "COM0004910"
"0000031" "COM0025171"
"0000032" "COM0021672"
"0000033" "COM0011561"
"0000034" "COM0024333"
"0000035" "COM0002822"
"0000036" "COM0006303"
"0000037" "COM0019917"
"0000038" "COM0005150"
"0000039" "COM0009544"
"0000040" "COM0002456"
"0000041" "COM0024887"
"0000042" "COM0017015"
"0000043" "COM0017561"
"0000044" "COM0006670"
"0000045" "COM0024779"
"0000046" "COM0016587"
"0000047" "COM0021096"
"0000048" "COM0000689"
"0000049" "COM0008269"
"0000050" "COM0025453"
"0000051" "COM0017803"
"0000052" "COM0015475"
"0000053" "COM0025361"
"0000054" "COM0000368"
"0000055" "COM0020352"
"0000056" "COM0001253"
"0000057" "COM0023842"
"0000058" ""          
"0000059" "COM0021121"
"0000060" "COM0004109"
"0000061" "COM0009751"
"0000062" "COM0022079"
"0000063" "COM0016751"
"0000064" "COM0017223"
"0000065" "COM0017150"
"0000066" "COM0012403"
"0000067" "COM0022711"
"0000068" "COM0011220"
"0000069" "COM0007839"
"0000070" "COM0011544"
"0000071" "COM0007227"
"0000072" "COM0015440"
"0000073" "COM0022381"
"0000074" "COM0003125"
"0000075" "COM0018218"
"0000076" "COM0019439"
"0000077" "COM0009966"
"0000078" "COM0020509"
"0000079" "COM0012767"
"0000080" "COM0018801"
"0000081" "COM0001058"
"0000082" "COM0002594"
"0000083" "COM0006943"
"0000084" "COM0002462"
"0000085" "COM0002917"
"0000086" "COM0009035"
"0000087" "COM0024918"
"0000088" "COM0018266"
"0000089" "COM0000556"
"0000090" "COM0007366"
"0000091" "COM0016009"
"0000092" "COM0006240"
"0000093" "COM0008398"
"0000094" "COM0018287"
"0000095" "COM0009495"
"0000096" "COM0002020"
"0000097" "COM0024751"
"0000098" "COM0024158"
"0000099" ""          
"0000100" "COM0018003"
end
        Here, I dropped households that don't have a corresponding COMID

        Code:
        drop if missing(COMID)
        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str7 HHID str10 COMID
"0000001" "COM0023725"
"0000002" "COM0001896"
"0000003" "COM0017866"
"0000004" "COM0016953"
"0000005" "COM0013514"
"0000006" "COM0015591"
"0000007" "COM0009260"
"0000008" "COM0019383"
"0000009" "COM0024513"
"0000010" "COM0022219"
"0000011" "COM0006835"
"0000012" "COM0007215"
"0000013" "COM0008162"
"0000014" "COM0005931"
"0000015" "COM0024866"
"0000016" "COM0006443"
"0000017" "COM0019976"
"0000018" "COM0020718"
"0000019" "COM0020574"
"0000020" "COM0022864"
"0000021" "COM0014013"
"0000022" "COM0017557"
"0000023" "COM0021959"
"0000024" "COM0023074"
"0000025" "COM0025825"
"0000026" "COM0009983"
"0000027" "COM0001879"
"0000028" "COM0001795"
"0000029" "COM0004882"
"0000030" "COM0004910"
"0000031" "COM0025171"
"0000032" "COM0021672"
"0000033" "COM0011561"
"0000034" "COM0024333"
"0000035" "COM0002822"
"0000036" "COM0006303"
"0000037" "COM0019917"
"0000038" "COM0005150"
"0000039" "COM0009544"
"0000040" "COM0002456"
"0000041" "COM0024887"
"0000042" "COM0017015"
"0000043" "COM0017561"
"0000044" "COM0006670"
"0000045" "COM0024779"
"0000046" "COM0016587"
"0000047" "COM0021096"
"0000048" "COM0000689"
"0000049" "COM0008269"
"0000050" "COM0025453"
"0000051" "COM0017803"
"0000052" "COM0015475"
"0000053" "COM0025361"
"0000054" "COM0000368"
"0000055" "COM0020352"
"0000056" "COM0001253"
"0000057" "COM0023842"
"0000059" "COM0021121"
"0000060" "COM0004109"
"0000061" "COM0009751"
"0000062" "COM0022079"
"0000063" "COM0016751"
"0000064" "COM0017223"
"0000065" "COM0017150"
"0000066" "COM0012403"
"0000067" "COM0022711"
"0000068" "COM0011220"
"0000069" "COM0007839"
"0000070" "COM0011544"
"0000071" "COM0007227"
"0000072" "COM0015440"
"0000073" "COM0022381"
"0000074" "COM0003125"
"0000075" "COM0018218"
"0000076" "COM0019439"
"0000077" "COM0009966"
"0000078" "COM0020509"
"0000079" "COM0012767"
"0000080" "COM0018801"
"0000081" "COM0001058"
"0000082" "COM0002594"
"0000083" "COM0006943"
"0000084" "COM0002462"
"0000085" "COM0002917"
"0000086" "COM0009035"
"0000087" "COM0024918"
"0000088" "COM0018266"
"0000089" "COM0000556"
"0000090" "COM0007366"
"0000091" "COM0016009"
"0000092" "COM0006240"
"0000093" "COM0008398"
"0000094" "COM0018287"
"0000095" "COM0009495"
"0000096" "COM0002020"
"0000097" "COM0024751"
"0000098" "COM0024158"
"0000100" "COM0018003"
"0000101" "COM0026553"
"0000102" "COM0000790"
end

        Where I provide additional variables from the merged datasets for Farminghousehold and Maaping dataset.

        Code:
        dataex HHID COMID state_code lga_code ea_code zone_id sector
        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str7 HHID str10 COMID byte state_code int lga_code str7 ea_code byte(zone_id sector)
"0000001" "COM0023725" 29 583 "0027201" 5 2
"0000002" "COM0001896"  9 173 "0035309" 2 2
"0000003" "COM0017866" 26 543 "0032804" 4 1
"0000004" "COM0016953" 10 213 "0059703" 3 2
"0000005" "COM0013514"  4  75 "0025504" 3 2
"0000006" "COM0015591"  1  10 "0089800" 3 2
"0000007" "COM0009260" 28 570 "0042302" 4 1
"0000008" "COM0019383" 26   . "0088202" 4 2
"0000009" "COM0024513"  8 169 "0057602" 2 2
"0000010" "COM0022219" 12 265 "0015304" 3 2
"0000011" "COM0006835"  7 142 "0025504" 2 1
"0000012" "COM0007215" 23 481 "0626700" 6 1
"0000013" "COM0008162"  3  38 "0113100" 3 2
"0000014" "COM0005931" 21 437 "0079900" 6 1
"0000015" "COM0024866"  4  96 "0020200" 3 2
"0000016" "COM0006443"  2  25 "0004806" 3 2
"0000017" "COM0019976" 16 328 "0084601" 1 2
"0000018" "COM0020718" 10 216 "0001503" 3 2
"0000019" "COM0020574"  .   . "0043401" 5 2
"0000020" "COM0022864" 22 454 "0190300" 6 2
"0000021" "COM0014013"  .   . "0054701" 1 2
"0000022" "COM0017557"  3  52 "0071900" 3 2
"0000023" "COM0021959" 21 445 "0007702" 6 1
"0000024" "COM0023074" 36 739 "0031701" 6 1
"0000025" "COM0025825" 22 469 "0028100" 6 2
"0000026" "COM0009983" 31 639 "0021706" 4 1
"0000027" "COM0001879"  .   . "0048900" 6 2
"0000028" "COM0001795"  4  93 "0076602" 3 1
"0000029" "COM0004882" 27 564 "0042301" 4 1
"0000030" "COM0004910" 17 353 "0339700" 2 2
"0000031" "COM0025171" 26 537 "0144000" 4 1
"0000032" "COM0021672" 21 431 "0024801" 6 2
"0000033" "COM0011561" 23 488 "0033001" 6 1
"0000034" "COM0024333" 13 296 "0041602" 1 2
"0000035" "COM0002822" 13 300 "0100703" 1 2
"0000036" "COM0006303" 17 356 "0120200" 2 2
"0000037" "COM0019917" 25 522 "0003001" 5 1
"0000038" "COM0005150" 26 546 "0015104" 4 1
"0000039" "COM0009544" 13 295 "0013302" 1 2
"0000040" "COM0002456"  .   . "0071900" 5 1
"0000041" "COM0024887" 21 432 "0204900" 6 2
"0000042" "COM0017015"  7 143 "0016902" 2 2
"0000043" "COM0017561" 26 533 "0053100" 4 2
"0000044" "COM0006670"  9 181 "0007900" 2 2
"0000045" "COM0024779" 30 626 "0004606" 5 2
"0000046" "COM0016587"  7 158 "0053702" 2 2
"0000047" "COM0021096" 22 454 "0186700" 6 1
"0000048" "COM0000689"  6 128 "0251300" 2 1
"0000049" "COM0008269" 13 305 "0045600" 1 2
"0000050" "COM0025453" 23 477 "0018903" 6 2
"0000051" "COM0017803" 10 223 "0037007" 3 2
"0000052" "COM0015475" 11 245 "0145700" 3 1
"0000053" "COM0025361"  8 166 "0019003" 2 2
"0000054" "COM0000368" 24 508 "0044003" 6 2
"0000055" "COM0020352"  8 168 "0064300" 2 2
"0000056" "COM0001253" 10   . "0096100" 3 2
"0000057" "COM0023842" 30 621 "0008703" 5 2
"0000059" "COM0021121" 32 661 "0051901" 4 2
"0000060" "COM0004109" 36   . "0040902" 6 1
"0000061" "COM0009751" 29 594 "0001301" 5 1
"0000062" "COM0022079" 32 652 "0015700" 4 1
"0000063" "COM0016751"  4  78 "0004606" 3 2
"0000064" "COM0017223" 23 483 "0089600" 6 2
"0000065" "COM0017150" 12 268 "0015104" 3 2
"0000066" "COM0012403" 29 583 "0263900" 5 1
"0000067" "COM0022711" 33 671 "0016506" 5 1
"0000068" "COM0011220" 37 760 "0044504" 6 2
"0000069" "COM0007839" 35 715 "0045600" 5 2
"0000070" "COM0011544" 24   . "0027001" 6 2
"0000071" "COM0007227" 25 517 "0027702" 5 1
"0000072" "COM0015440" 32 670 "0087404" 4 2
"0000073" "COM0022381"  8 168 "0272300" 2 2
"0000074" "COM0003125" 33 689 "0126400" 5 1
"0000075" "COM0018218" 19   . "0194500" 1 2
"0000076" "COM0019439" 22 448 "0016500" 6 2
"0000077" "COM0009966" 22 452 "0015301" 6 1
"0000078" "COM0020509"  5  99 "0014000" 2 2
"0000079" "COM0012767"  3  42 "0137400" 3 2
"0000080" "COM0018801"  3  71 "0100700" 3 1
"0000081" "COM0001058" 14 311 "0014101" 1 2
"0000082" "COM0002594"  3  57 "0204900" 3 2
"0000083" "COM0006943" 25 514 "0138600" 5 2
"0000084" "COM0002462" 23 477 "0263800" 6 1
"0000085" "COM0002917"  2  36 "0013803" 3 1
"0000086" "COM0009035" 31 644 "0039404" 4 1
"0000087" "COM0024918"  2  29 "0141200" 3 2
"0000088" "COM0018266" 30   . "0023103" 5 2
"0000089" "COM0000556" 31 637 "0007200" 4 1
"0000090" "COM0007366" 15 313 "0038900" 1 2
"0000091" "COM0016009" 19   . "0009001" 1 2
"0000092" "COM0006240" 24 496 "0043500" 6 2
"0000093" "COM0008398" 22 463 "0031308" 6 1
"0000094" "COM0018287" 21 416 "0125000" 6 1
"0000095" "COM0009495" 17 346 "0004801" 2 2
"0000096" "COM0002020" 12 261 "0059703" 3 2
"0000097" "COM0024751" 16 331 "0001401" 1 2
"0000098" "COM0024158"  6 136 "0078300" 2 1
"0000100" "COM0018003" 33 685 "0030802" 5 1
"0000101" "COM0026553" 22 454 "0001501" 6 1
"0000102" "COM0000790" 35   . "0055804" 5 1
end
label values state_code labels0
label def labels0 1 "SOKOTO", modify
label def labels0 2 "ZAMFARA", modify
label def labels0 3 "KATSINA", modify
label def labels0 4 "JIGAWA", modify
label def labels0 5 "YOBE", modify
label def labels0 6 "BORNO", modify
label def labels0 7 "ADAMAWA", modify
label def labels0 8 "GOMBE", modify
label def labels0 9 "BAUCHI", modify
label def labels0 10 "KANO", modify
label def labels0 11 "KADUNA", modify
label def labels0 12 "KEBBI", modify
label def labels0 13 "NIGER", modify
label def labels0 14 "FCT", modify
label def labels0 15 "NASARAWA", modify
label def labels0 16 "PLATEAU", modify
label def labels0 17 "TARABA", modify
label def labels0 19 "KOGI", modify
label def labels0 21 "OYO", modify
label def labels0 22 "OSUN", modify
label def labels0 23 "EKITI", modify
label def labels0 24 "ONDO", modify
label def labels0 25 "EDO", modify
label def labels0 26 "ANAMBRA", modify
label def labels0 27 "ENUGU", modify
label def labels0 28 "EBONYI", modify
label def labels0 29 "CROSS RIVER", modify
label def labels0 30 "AKWA IBOM", modify
label def labels0 31 "ABIA", modify
label def labels0 32 "IMO", modify
label def labels0 33 "RIVERS", modify
label def labels0 35 "DELTA", modify
label def labels0 36 "LAGOS", modify
label def labels0 37 "OGUN", modify
label values lga_code labels1
label def labels1 10 "WURNO", modify
label def labels1 25 "BUKKUYUM", modify
label def labels1 29 "MARADUN", modify
label def labels1 36 "BUNGUDU", modify
label def labels1 38 "BAURE", modify
label def labels1 42 "DAURA", modify
label def labels1 52 "INGAWA", modify
label def labels1 57 "SAFANA", modify
label def labels1 71 "DANJA", modify
label def labels1 75 "KAZAURE", modify
label def labels1 78 "SULE TANKARKAR", modify
label def labels1 93 "JAHUN", modify
label def labels1 96 "BIRNIN KUDU", modify
label def labels1 99 "MACHINA", modify
label def labels1 128 "DIKWA", modify
label def labels1 136 "BIU", modify
label def labels1 142 "MICHIKA", modify
label def labels1 143 "MUBI  NORTH", modify
label def labels1 158 "MAYO-BELWA", modify
label def labels1 166 "AKKO", modify
label def labels1 168 "YAMALTU-DEBA", modify
label def labels1 169 "BALANGA", modify
label def labels1 173 "ZAKI", modify
label def labels1 181 "DAMBAN", modify
label def labels1 213 "WARAWA", modify
label def labels1 216 "WUDIL", modify
label def labels1 223 "ROGO", modify
label def labels1 245 "ZARIA", modify
label def labels1 261 "AUGIE", modify
label def labels1 265 "ALIERO", modify
label def labels1 268 "KALGO", modify
label def labels1 295 "MOKWA", modify
label def labels1 296 "EDATTI", modify
label def labels1 300 "AGAIE", modify
label def labels1 305 "TAFA", modify
label def labels1 311 "KUJE", modify
label def labels1 313 "KEFFI", modify
label def labels1 328 "RIYOM", modify
label def labels1 331 "BOKKOS", modify
label def labels1 346 "ZING", modify
label def labels1 353 "KURMI", modify
label def labels1 356 "DONGA", modify
label def labels1 416 "OLORUNSOGO", modify
label def labels1 431 "KAJOLA", modify
label def labels1 432 "IWAJOWA", modify
label def labels1 437 "AFIJIO", modify
label def labels1 445 "EGBEDA", modify
label def labels1 448 "IFEDAYO", modify
label def labels1 452 "OLORUNDA", modify
label def labels1 454 "EGBEDORE", modify
label def labels1 463 "EDE SOUTH", modify
label def labels1 469 "ISOKAN", modify
label def labels1 477 "ILEJEMEJE", modify
label def labels1 481 "GBONYIN", modify
label def labels1 483 "IREPODUN/IFELODUN", modify
label def labels1 488 "EKITI SOUTH WEST", modify
label def labels1 496 "OSE", modify
label def labels1 508 "ESE-ODO", modify
label def labels1 514 "OWAN EAST", modify
label def labels1 517 "ESAN CENTRAL", modify
label def labels1 522 "OVIA NORTH EAST", modify
label def labels1 533 "DUNUKOFIA", modify
label def labels1 537 "ANAOCHA", modify
label def labels1 543 "NNEWI NORTH", modify
label def labels1 546 "NNEWI SOUTH", modify
label def labels1 564 "AWGU", modify
label def labels1 570 "ABAKALIKI", modify
label def labels1 583 "OBANLIKU", modify
label def labels1 594 "CALABAR-MUNICIPAL", modify
label def labels1 621 "ESIT EKET", modify
label def labels1 626 "EASTERN OBOLO", modify
label def labels1 637 "ISIALA-NGWA SOUTH", modify
label def labels1 639 "ABA NORTH", modify
label def labels1 644 "UKWA WEST", modify
label def labels1 652 "OGUTA", modify
label def labels1 661 "OBOWO", modify
label def labels1 670 "NGOR-OKPALA", modify
label def labels1 671 "OGBA/EGBEMA/NDONI", modify
label def labels1 685 "ELEME", modify
label def labels1 689 "TAI", modify
label def labels1 715 "PATANI", modify
label def labels1 739 "LAGOS MAIN LAND", modify
label def labels1 760 "IJEBU NORTH", modify
label values zone_id labels2
label def labels2 1 "North Central", modify
label def labels2 2 "North East", modify
label def labels2 3 "North West", modify
label def labels2 4 "South East", modify
label def labels2 5 "South South", modify
label def labels2 6 "South West", modify
label values sector labels3
label def labels3 1 "URBAN", modify
label def labels3 2 "RURAL", modify

        Now merging 1_Identification_ano.dta which has unique COMID present. HHID is not included in this dataset. It merged successfully.

        Code:
        merge m:1 COMID using "1_Identification_ano.dta", keep(master match) nogen

        However, when I begin to merge other community datasets using this codes.

        Code:
        merge m:1 COMID using "2_Respondents_ano.dta", keep(master match) nogen
merge m:1 COMID using "3_c2q6_Electricity_ano.dta", keep(master match) nogen
merge m:1 COMID using "4_c2c_Market_Access_ano.dta", keep(master match) nogen
merge m:1 COMID using "5_c2d_input_support_ano.dta", keep(master match) nogen
merge m:1 COMID using "6_c2e_conflicts_ano.dta", keep(master match) nogen
merge m:1 COMID using "7_c4b_Disaster_ano.dta"", keep(master match) nogen

        Here is an example data for 2_Respondents_ano.dta

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str10 COMID double(zone_id state_id sector COMM_RESP__id)
"COM0001918" 6 30 1  2
"COM0001918" 6 30 1  7
"COM0001918" 6 30 1  6
"COM0001918" 6 30 1  3
"COM0001918" 6 30 1  4
"COM0001918" 6 30 1  5
"COM0001918" 6 30 1  1
"COM0007902" 5  3 2  3
"COM0007902" 5  3 2  2
"COM0007902" 5  3 2  1
"COM0007902" 5  3 2 10
"COM0007902" 5  3 2  6
"COM0007902" 5  3 2  4
"COM0007902" 5  3 2  9
"COM0007902" 5  3 2  8
"COM0007902" 5  3 2  5
"COM0007902" 5  3 2  7
"COM0018788" 5 12 1  8
"COM0018788" 5 12 1  7
"COM0018788" 5 12 1  1
"COM0018788" 5 12 1  3
"COM0018788" 5 12 1  4
"COM0018788" 5 12 1  2
"COM0018788" 5 12 1  9
"COM0018788" 5 12 1 10
"COM0018788" 5 12 1  5
"COM0018788" 5 12 1  6
"COM0007412" 3 34 2  3
"COM0007412" 3 34 2  7
"COM0007412" 3 34 2  4
"COM0007412" 3 34 2  2
"COM0007412" 3 34 2  6
"COM0007412" 3 34 2  1
"COM0009938" 5  9 2  6
"COM0009938" 5  9 2  4
"COM0009938" 5  9 2  2
"COM0009938" 5  9 2  1
"COM0009938" 5  9 2  5
"COM0009938" 5  9 2  3
"COM0010711" 3 22 2  3
"COM0010711" 3 22 2  7
"COM0010711" 3 22 2  5
"COM0010711" 3 22 2  2
"COM0010711" 3 22 2  4
"COM0010711" 3 22 2  6
"COM0010711" 3 22 2  1
"COM0004402" 6 28 1  4
"COM0004402" 6 28 1  6
"COM0004402" 6 28 1  3
"COM0004402" 6 28 1  7
"COM0004402" 6 28 1  5
"COM0004402" 6 28 1  1
"COM0004402" 6 28 1  2
"COM0024902" 5  6 2  8
"COM0024902" 5  6 2  9
"COM0024902" 5  6 2  5
"COM0024902" 5  6 2  6
"COM0024902" 5  6 2  1
"COM0024902" 5  6 2  7
"COM0024902" 5  6 2  2
"COM0024902" 5  6 2  3
"COM0024902" 5  6 2 10
"COM0024902" 5  6 2  4
"COM0016004" 3 20 2  6
"COM0016004" 3 20 2 10
"COM0016004" 3 20 2  5
"COM0016004" 3 20 2  2
"COM0016004" 3 20 2  9
"COM0016004" 3 20 2  3
"COM0016004" 3 20 2  7
"COM0016004" 3 20 2  4
"COM0016004" 3 20 2  8
"COM0016004" 3 20 2  1
"COM0013418" 3 18 1  2
"COM0013418" 3 18 1  1
"COM0013418" 3 18 1  4
"COM0013418" 3 18 1  3
"COM0013418" 3 18 1  6
"COM0013418" 3 18 1  5
"COM0002459" 2  5 2  5
"COM0002459" 2  5 2  1
"COM0002459" 2  5 2  3
"COM0002459" 2  5 2  7
"COM0002459" 2  5 2  2
"COM0002459" 2  5 2  4
"COM0002459" 2  5 2  6
"COM0020280" 3 18 2  2
"COM0020280" 3 18 2  3
"COM0020280" 3 18 2  1
"COM0020280" 3 18 2  6
"COM0020280" 3 18 2  5
"COM0020280" 3 18 2  4
"COM0002133" 4  1 1  1
"COM0002133" 4  1 1 10
"COM0002133" 4  1 1  9
"COM0002133" 4  1 1  4
"COM0002133" 4  1 1  6
"COM0002133" 4  1 1  7
"COM0002133" 4  1 1  2
"COM0002133" 4  1 1  8
end
label values zone_id zone_id
label def zone_id 2 "North East", modify
label def zone_id 3 "North West", modify
label def zone_id 4 "South East", modify
label def zone_id 5 "South South", modify
label def zone_id 6 "South West", modify
label values state_id state_id
label def state_id 1 "ABIA", modify
label def state_id 3 "AKWA IBOM", modify
label def state_id 5 "BAUCHI", modify
label def state_id 6 "BAYELSA", modify
label def state_id 9 "CROSS RIVER", modify
label def state_id 12 "EDO", modify
label def state_id 18 "JIGAWA", modify
label def state_id 20 "KANO", modify
label def state_id 22 "KEBBI", modify
label def state_id 28 "OGUN", modify
label def state_id 30 "OSUN", modify
label def state_id 34 "SOKOTO", modify
label values sector sector
label def sector 1 "Urban", modify
label def sector 2 "Rural", modify

        Here is an example data for 3_c2q6_Electricity_ano.dta

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str10 COMID double(zone_id state_id sector c2q6__id)
"COM0003870" 1 23 2   1
"COM0000018" 1  7 2   1
"COM0009802" 1 23 1   1
"COM0017187" 1 27 2   1
"COM0000051" 1 32 2   1
"COM0001608" 1  7 1   1
"COM0012954" 1 15 1   1
"COM0015888" 1 23 1   1
"COM0018288" 1 32 2   1
"COM0012707" 1 23 1   1
"COM0019457" 1 27 1   1
"COM0003241" 1 27 1   1
"COM0025423" 1  7 2   1
"COM0000117" 1 26 2   1
"COM0003007" 1 27 1   1
"COM0007886" 1 23 2   1
"COM0025803" 1  7 2   1
"COM0012193" 1  7 1   1
"COM0025668" 1 27 1   1
"COM0012687" 1 23 2   1
"COM0023282" 1 26 1   1
"COM0001109" 1  7 2   1
"COM0016055" 1 32 1   1
"COM0000588" 1 24 1   1
"COM0012260" 1 26 2   1
"COM0024896" 1 26 1   1
"COM0007780" 1 27 2   3
"COM0021549" 1 27 1   1
"COM0011987" 1 27 1   1
"COM0007108" 1 26 1   1
"COM0005894" 1 24 1   1
"COM0003176" 1 23 2   1
"COM0011694" 1 15 1   1
"COM0008596" 1  7 2   1
"COM0006148" 1 27 2   1
"COM0025335" 1 32 1   1
"COM0002627" 1  7 1   1
"COM0002555" 1  7 1   1
"COM0001396" 1  7 2   1
"COM0002915" 1 23 2   3
"COM0025096" 1 23 2   1
"COM0022968" 1 24 2   1
"COM0021989" 1 24 1   1
"COM0009559" 1 27 2   1
"COM0015566" 1  7 2   1
"COM0018718" 1 32 1   1
"COM0008962" 1 23 1   1
"COM0018320" 1  7 2   1
"COM0000079" 1 23 2   1
"COM0021282" 1 24 1   1
"COM0025428" 1 27 1   1
"COM0021726" 1  7 1   1
"COM0022702" 1 24 2   1
"COM0012705" 1 26 1   1
"COM0025606" 1 32 2 999
"COM0024483" 1 15 1   4
"COM0025835" 1 23 2   1
"COM0001177" 1 27 2   4
"COM0012988" 1 15 1   4
"COM0008447" 1 24 2   1
"COM0007800" 1 24 1   1
"COM0004130" 1 27 2   1
"COM0012402" 1  7 1   1
"COM0005208" 1 26 1   2
"COM0023987" 1 23 2   1
"COM0002792" 1 26 1   1
"COM0011630" 1 32 2   1
"COM0011567" 1 27 2   1
"COM0008269" 1 27 2   1
"COM0015117" 1 32 1 999
"COM0004780" 1 15 1   1
"COM0025261" 1 27 1   1
"COM0017002" 1 26 1   1
"COM0021383" 1 27 2   1
"COM0025137" 1 24 2   1
"COM0005815" 1 26 1   4
"COM0017978" 1 27 2   1
"COM0021194" 1  7 1   1
"COM0026284" 1 24 2   1
"COM0003331" 1 24 1   1
"COM0000068" 1 27 1   1
"COM0004173" 1  7 1   1
"COM0015403" 1 27 2   1
"COM0012517" 1 32 2   1
"COM0013316" 1 26 1   1
"COM0006478" 1 23 2   1
"COM0009682" 1 27 2   1
"COM0003627" 1 23 1   1
"COM0019858" 1 23 2   3
"COM0016363" 1 32 1   1
"COM0004077" 1 27 2   1
"COM0020879" 1  7 2   1
"COM0018648" 1 23 2   1
"COM0020716" 1 24 2   1
"COM0009147" 1 32 2 999
"COM0015132" 1  7 1   1
"COM0013006" 1 27 2   1
"COM0015828" 1 27 1   1
"COM0004475" 1 24 2   1
"COM0017956" 1  7 2   1
end
label values zone_id zone_id
label def zone_id 1 "North Central", modify
label values state_id state_id
label def state_id 7 "BENUE", modify
label def state_id 15 "FCT", modify
label def state_id 23 "KOGI", modify
label def state_id 24 "KWARA", modify
label def state_id 26 "NASARAWA", modify
label def state_id 27 "NIGER", modify
label def state_id 32 "PLATEAU", modify
label values sector sector
label def sector 1 "Urban", modify
label def sector 2 "Rural", modify
label values c2q6__id c2q6__id
label def c2q6__id 1 "National grid (PHCN)", modify
label def c2q6__id 2 "Community mini-grid", modify
label def c2q6__id 3 "Community solar", modify
label def c2q6__id 4 "Community generator", modify
label def c2q6__id 999 "Other(s) Specify", modify



        Here is an example data for 4_c2c_Market_Access_ano.dta

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str10 COMID double(zone_id state_id sector c2c__id)
"COM0016738" 1 26 2 1
"COM0019665" 3 20 1 1
"COM0008626" 1  7 2 1
"COM0001211" 2  5 2 1
"COM0018464" 6 25 1 1
"COM0000333" 2  5 2 1
"COM0007006" 6 31 1 1
"COM0004201" 5  9 1 1
"COM0010363" 2 36 2 1
"COM0008151" 1 26 2 1
"COM0026304" 1  7 1 1
"COM0018106" 1 15 1 1
"COM0016296" 2 35 1 1
"COM0009712" 1 32 2 1
"COM0007658" 3 34 1 1
"COM0012322" 6 25 1 1
"COM0004337" 3 20 2 1
"COM0024157" 3 20 2 1
"COM0009532" 1 24 2 1
"COM0024841" 4 14 1 1
"COM0025194" 1 15 2 1
"COM0005890" 1 26 1 1
"COM0011857" 4  4 1 1
"COM0002211" 1  7 2 1
"COM0021101" 4 11 1 1
"COM0021933" 3 18 2 1
"COM0016419" 4 14 1 1
"COM0005658" 5 10 1 1
"COM0015431" 4 11 2 1
"COM0005025" 3 21 1 1
"COM0016909" 2  2 2 1
"COM0013137" 1 27 2 1
"COM0019259" 6 13 1 1
"COM0001639" 3 19 2 1
"COM0006753" 3 20 2 1
"COM0009462" 1 23 2 1
"COM0004962" 4  4 1 1
"COM0007232" 3 20 1 1
"COM0023040" 4  4 1 1
"COM0013242" 6 29 2 1
"COM0001958" 1  7 2 1
"COM0025117" 4  4 1 1
"COM0009137" 2 36 2 1
"COM0000692" 3 18 2 1
"COM0025663" 2  8 1 1
"COM0007348" 3 19 2 1
"COM0004413" 2 35 1 1
"COM0013768" 3 20 1 1
"COM0023279" 1 15 2 1
"COM0002299" 1 27 2 1
"COM0015778" 3 19 2 1
"COM0014577" 1 27 2 1
"COM0002234" 5  6 1 1
"COM0024134" 6 30 1 1
"COM0013602" 3 37 1 1
"COM0009517" 2  8 1 1
"COM0000322" 1  7 2 1
"COM0007188" 1 27 1 1
"COM0026477" 5 10 1 1
"COM0023954" 1 15 2 1
"COM0000869" 1 23 1 1
"COM0006567" 5  9 2 1
"COM0011268" 1 27 1 1
"COM0019925" 1 26 2 1
"COM0022246" 5  3 2 1
"COM0008947" 5  3 2 1
"COM0000344" 3 21 1 1
"COM0017960" 5 10 1 1
"COM0002529" 1  7 2 1
"COM0024629" 2 16 1 1
"COM0002981" 3 19 2 1
"COM0006888" 3 22 2 1
"COM0020543" 1  7 2 1
"COM0018401" 1 27 1 1
"COM0011215" 5  3 2 1
"COM0023253" 1 26 2 1
"COM0024690" 1 24 2 1
"COM0017495" 3 37 2 1
"COM0022272" 1 27 2 1
"COM0018676" 3 21 1 1
"COM0014204" 3 34 2 1
"COM0003232" 3 21 2 1
"COM0016041" 5  3 1 1
"COM0005733" 5 10 1 1
"COM0023454" 5 10 2 1
"COM0014522" 1 23 2 1
"COM0021018" 3 34 2 1
"COM0024692" 1 27 1 1
"COM0020007" 6 28 2 1
"COM0010294" 1 26 2 1
"COM0007362" 1 26 2 1
"COM0001412" 2  5 2 1
"COM0010348" 5  3 2 1
"COM0005531" 3 20 2 1
"COM0025353" 6 25 1 1
"COM0003155" 6 30 2 1
"COM0014068" 3 21 2 1
"COM0020278" 2  8 2 1
"COM0014236" 1 24 2 1
"COM0009279" 2  5 2 1
end
label values zone_id zone_id
label def zone_id 1 "North Central", modify
label def zone_id 2 "North East", modify
label def zone_id 3 "North West", modify
label def zone_id 4 "South East", modify
label def zone_id 5 "South South", modify
label def zone_id 6 "South West", modify
label values state_id state_id
label def state_id 2 "ADAMAWA", modify
label def state_id 3 "AKWA IBOM", modify
label def state_id 4 "ANAMBRA", modify
label def state_id 5 "BAUCHI", modify
label def state_id 6 "BAYELSA", modify
label def state_id 7 "BENUE", modify
label def state_id 8 "BORNO", modify
label def state_id 9 "CROSS RIVER", modify
label def state_id 10 "DELTA", modify
label def state_id 11 "EBONYI", modify
label def state_id 13 "EKITI", modify
label def state_id 14 "ENUGU", modify
label def state_id 15 "FCT", modify
label def state_id 16 "GOMBE", modify
label def state_id 18 "JIGAWA", modify
label def state_id 19 "KADUNA", modify
label def state_id 20 "KANO", modify
label def state_id 21 "KATSINA", modify
label def state_id 22 "KEBBI", modify
label def state_id 23 "KOGI", modify
label def state_id 24 "KWARA", modify
label def state_id 25 "LAGOS", modify
label def state_id 26 "NASARAWA", modify
label def state_id 27 "NIGER", modify
label def state_id 28 "OGUN", modify
label def state_id 29 "ONDO", modify
label def state_id 30 "OSUN", modify
label def state_id 31 "OYO", modify
label def state_id 32 "PLATEAU", modify
label def state_id 34 "SOKOTO", modify
label def state_id 35 "TARABA", modify
label def state_id 36 "YOBE", modify
label def state_id 37 "ZAMFARA", modify
label values sector sector
label def sector 1 "Urban", modify
label def sector 2 "Rural", modify
label values c2c__id c2c__id
label def c2c__id 1 "Wholesale market", modify

        Here is an example data for 5_c2d_input_support_ano.dta

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str10 COMID double(zone_id state_id sector c2d__id)
"COM0011728" 1 7 1 1
"COM0007927" 1 7 2 1
"COM0026262" 1 7 2 1
"COM0005948" 1 7 1 1
"COM0003994" 1 7 1 1
"COM0008252" 1 7 2 1
"COM0003594" 1 7 2 1
"COM0011040" 1 7 2 1
"COM0000830" 1 7 2 1
"COM0016215" 1 7 1 1
"COM0014259" 1 7 1 1
"COM0013359" 1 7 2 1
"COM0024151" 1 7 2 1
"COM0016869" 1 7 1 1
"COM0024090" 1 7 2 1
"COM0004029" 1 7 2 1
"COM0013236" 1 7 2 1
"COM0013327" 1 7 1 1
"COM0007727" 1 7 1 1
"COM0002754" 1 7 2 1
"COM0013878" 1 7 2 1
"COM0011120" 1 7 1 1
"COM0005507" 1 7 2 1
"COM0004351" 1 7 1 1
"COM0022806" 1 7 1 1
"COM0017755" 1 7 2 1
"COM0017651" 1 7 2 1
"COM0001612" 1 7 2 1
"COM0020432" 1 7 2 1
"COM0021818" 1 7 1 1
"COM0013702" 1 7 1 1
"COM0026121" 1 7 2 1
"COM0014201" 1 7 1 1
"COM0025403" 1 7 1 1
"COM0015045" 1 7 2 1
"COM0024271" 1 7 1 1
"COM0021535" 1 7 1 1
"COM0003037" 1 7 2 1
"COM0018320" 1 7 2 1
"COM0026384" 1 7 2 1
"COM0022372" 1 7 2 1
"COM0015851" 1 7 2 1
"COM0017273" 1 7 2 1
"COM0023382" 1 7 2 1
"COM0019519" 1 7 1 1
"COM0004173" 1 7 1 1
"COM0003424" 1 7 2 1
"COM0016649" 1 7 2 1
"COM0024505" 1 7 1 1
"COM0016538" 1 7 2 1
"COM0021171" 1 7 1 1
"COM0007980" 1 7 1 1
"COM0011037" 1 7 1 1
"COM0005844" 1 7 1 1
"COM0014431" 1 7 2 1
"COM0010124" 1 7 2 1
"COM0001153" 1 7 2 1
"COM0002622" 1 7 2 1
"COM0006992" 1 7 1 1
"COM0011880" 1 7 1 1
"COM0005786" 1 7 2 1
"COM0013960" 1 7 2 1
"COM0016463" 1 7 2 1
"COM0005152" 1 7 1 1
"COM0011952" 1 7 1 1
"COM0013644" 1 7 2 1
"COM0015127" 1 7 2 1
"COM0020421" 1 7 1 1
"COM0012094" 1 7 2 1
"COM0012402" 1 7 1 1
"COM0016441" 1 7 2 1
"COM0010353" 1 7 1 1
"COM0026457" 1 7 2 1
"COM0020294" 1 7 2 1
"COM0017850" 1 7 2 1
"COM0016449" 1 7 2 1
"COM0000257" 1 7 1 1
"COM0011077" 1 7 2 1
"COM0008152" 1 7 2 1
"COM0002001" 1 7 1 1
"COM0016180" 1 7 1 1
"COM0012883" 1 7 2 1
"COM0025100" 1 7 1 1
"COM0024058" 1 7 2 1
"COM0002675" 1 7 2 1
"COM0004135" 1 7 1 1
"COM0014918" 1 7 2 1
"COM0016862" 1 7 2 1
"COM0005787" 1 7 1 1
"COM0009548" 1 7 2 1
"COM0020087" 1 7 2 1
"COM0001597" 1 7 1 1
"COM0015754" 1 7 2 1
"COM0008031" 1 7 2 1
"COM0000526" 1 7 1 1
"COM0022714" 1 7 1 1
"COM0010882" 1 7 2 1
"COM0016161" 1 7 2 1
"COM0018980" 1 7 1 1
"COM0006691" 1 7 1 1
end
label values zone_id zone_id
label def zone_id 1 "North Central", modify
label values state_id state_id
label def state_id 7 "BENUE", modify
label values sector sector
label def sector 1 "Urban", modify
label def sector 2 "Rural", modify
label values c2d__id c2d__id
label def c2d__id 1 "Input supplier - Fertiliser shop", modify

        I would appreciate any help.

        Thank you

        Comment

        • #5
          Your merge command for 3_c2q6_Electricity_ano.dta assumes that COMID is unique in that dataset (since you are doing an m:1 merge), but this is false. There are observations with COMID missing, and there are several duplicates.

          Code:
          . codebook COMID

---------------------------------------------------------------------------------------------------------------------------------------------------------
COMID                                                                                                                                         (unlabeled)
---------------------------------------------------------------------------------------------------------------------------------------------------------

                  Type: String (str10)

         Unique values: 15,004                    Missing "": 23/16,906

              Examples: "COM0005318"
                        "COM0010638"
                        "COM0015939"
                        "COM0021370"

. duplicates tag COMID, gen(tag)

Duplicates in terms of COMID

. tab tag

        tag |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |     13,479       79.73       79.73
          1 |      2,470       14.61       94.34
          2 |        678        4.01       98.35
          3 |        256        1.51       99.86
         22 |         23        0.14      100.00
------------+-----------------------------------
      Total |     16,906      100.00

          Comment

          • #6
            Yes, as Hemanshu Kumar points out, COMID has both missings and repeats in the data. Looking at the posted examples it seems that the minimal group of variables that identifies unique observations in the *_ano.dta data sets is COMID zone state sector plus another variable which is differently named in the different data sets. Given that this variable is differently named in the different data sets, it is questionable whether it can even be used after renaming because perhaps the person corresponding to a given value of c2d_id is not the same person who has that value for c2c_id and those other variables. Based on what is shown, it is not possible to say how these data sets can be joined together.

            In situations like this, the curator of the original data sets usually provides documentation that explains how to match up the same respondents in the different data sets. It cannot be discerned from inspection of the data shown. So you must obtain that documentation, read it, and follow its instructions. If when you have those instructions it is unclear how to "translate" them into Stata code, do post back and explain what the instructions are.

            Comment

            Previous Next
            Working...
            X