Announcement

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

    Same ID but do not get merged

    Dear Statalist Users,

    I am trying to merge and individual level data with another dataset at city level.
    The common identifier in both datasets is "plate" which spans from 1 to 81 in both datasets. It refers the city's unique numbers (for example, for Adana city it is 1). However, whenever I try to merge these two datasets, I receive the same error: "variable plate does not uniquely identify observations in the master data"

    Can somebody please help me? Thank you in advance.

    This is individual level data:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input float plate str15 CASEID double(V024 haz06 waz06)
1 "      190906 02" 2                -.39                 .38
1 "      1910 9  2" 2                   .                   .
1 "      191210 02" 2                   .                   .
1 "      191112  2" 2                 .29                1.22
1 "      190608 02" 2                -.42                 .01
1 "      210103 02" 2                 .39                 .73
1 "      200113 04" 2                  .5                -.37
1 "    08320011 02" 2                -.02                -.18
1 "      200122  2" 2               -2.02               -1.16
1 "      1909 1  2" 2                   0                 .96
1 "      210317 02" 2                   .                   .
1 "    08320010 02" 2                  -2                -1.2
1 "      200125  2" 2               -1.83                -.63
1 "    08350020 02" 2  -.5700000000000001  .41000000000000003
1 "      191120 04" 2                   .                   .
1 "      210313 04" 2 -1.3900000000000001                 .06
1 "      200213 02" 2                 .65                 .91
1 "      190301 02" 2                1.04                -.15
1 "      190214 03" 2                  .1                -.23
1 "      1915 7  2" 2               -1.05                -.42
1 "    08400006 02" 2                   .                   .
1 "      1911 9  2" 2                   .                   .
1 "      190211 02" 2                 .44                  .8
1 "      190310 02" 2                  .9                 .46
1 "      210314  2" 2                -.37                 .33
1 "    08290010 03" 2                 .27                  .6
1 "      200104 02" 2                 .93                1.86
1 "      191111 02" 2                 -.1                  .3
1 "      200216 02" 2                   .                   .
1 "      1904 5  2" 2                  .5                1.94
1 "      2104 6  2" 2 -.41000000000000003                -.11
1 "      191315  2" 2  .47000000000000003                 .77
1 "      190609 02" 2                 .29                -.79
1 "    08360003 02" 2               -3.37               -1.18
1 "      1915 4  2" 2                 -.2                 .24
1 "      191625 02" 2 -1.1500000000000001                 .36
1 "      190315  3" 2                 .71                 .45
1 "    08350018 02" 2                -.79                -.29
1 "      1904 3  1" 2                -.78                 .66
1 "      200122 02" 2                 .16                 .13
1 "      191621 02" 2                 .43                -.44
1 "      191319  2" 2                 .36                  .4
1 "    08420014 02" 2                -.65                -.71
1 "      190311 02" 2                 .13                 .59
1 "      210406 02" 2               -1.35                -.81
1 "      190813  2" 2                   .                   .
1 "      2103 1  2" 2                1.96                  .3
1 "    08270008 02" 2                -.32                 .12
1 "      190112  2" 2                  .6                1.28
1 "      2001 5  3" 2                 .39                 .97
1 "      191201 02" 2 -1.6300000000000001                -.18
1 "      191209 02" 2                   .                   .
1 "    08400010 02" 2               -1.18                -.46
1 "      190123  2" 2                   .                   .
1 "      2104 8  2" 2                -.15                -.02
1 "      191320  2" 2               -2.24               -1.58
1 "      200102 02" 2                -.78 -.47000000000000003
1 "      191217 02" 2                 .09                -.19
1 "      190414  2" 2                1.17                2.48
1 "      190425  2" 2               -3.69                  -2
1 "      190320  2" 2                   .                   .
1 "      200121 01" 2 -.35000000000000003                 .21
1 "    08400008 02" 2                1.53  1.6500000000000001
1 "      190309 02" 2                -.45                -.62
1 "      190222 02" 2  3.2600000000000002                3.12
1 "    08320016 02" 2                 .06  -.9400000000000001
1 "      190305 02" 2                1.98                 .21
1 "      190502 02" 2                  .4                1.19
1 "      210113  2" 2                -.14  1.6600000000000001
1 "      191512 02" 2                   .                   .
1 "      191602 02" 2                   .                   .
1 "      191511  2" 2                   .                   .
1 "      190712 01" 2                 .09                  .1
1 "      200201 04" 2                -.51  1.6300000000000001
1 "    08280007 02" 2                 .89                 .56
1 "      190304 02" 2                 .11                1.35
1 "      191021  2" 2                -.13                 .17
1 "      191015  2" 2                1.62                1.31
1 "      2101 6  2" 2                -.42 -.41000000000000003
1 "      191009 02" 2                   .                   .
1 "      190803 02" 2                1.76                1.09
1 "      191418  2" 2                -.43                -.07
1 "    08270021 02" 2                2.93                 .84
1 "      190814 02" 2                -.03                 .76
1 "      200212 02" 2                 .24                 .49
1 "      1913 8  2" 2                -.14                 1.1
1 "      190206 02" 2 -1.8800000000000001                 -.4
1 "      191524  2" 2               -1.05                -.63
1 "      2103 8  2" 2                1.84                1.09
1 "      190124  2" 2                   .                   .
1 "      1904 9  6" 2                   .                   .
1 "      190119  2" 2               -2.94                -.72
1 "      210217 02" 2               -1.28                -.55
1 "      191422  2" 2                1.09                 .55
1 "      190409 02" 2                1.05                 .23
1 "    08400018 03" 2               -4.59               -1.93
1 "      191008 02" 2                -.31                -.12
1 "    08280014 02" 2 -1.1400000000000001               -1.25
1 "      190611 02" 2               -1.11                -.52
1 "      1914 7  2" 2                   .                   .
end
label values V024 V024
label def V024 2 "South", modify

    This is city level data:


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input byte plate int year_of_birth str13 city double refpop long(initialpopulation p) int yob long rn_
1 2003 "Adana"                        0 2085225       . 2003      .
1 2004 "Adana"                        0 2085225       . 2004      .
1 2005 "Adana"                        0 2085225       . 2005      .
1 2006 "Adana"                        0 2085225       . 2006      .
1 2007 "Adana"                        0 2085225       . 2007      .
1 2008 "Adana"                        0 2085225       . 2008      .
1 2009 "Adana"                        0 2085225       . 2009      .
1 2010 "Adana"                        0 2085225 2085225 2010      0
1 2011 "Adana"                        0 2085225 2108805 2011      0
1 2012 "Adana"                        . 2085225       . 2012      .
1 2013 "Adana"       .00927333980745483 2085225 2149260 2013  19337
1 2014 "Adana"      .029361819467923125 2085225 2165595 2014  61226
1 2015 "Adana"       .06555551559184261 2085225 2183167 2015 136698
1 2016 "Adana"        .0718090373940462 2085225 2201670 2016 149738
1 2017 "Adana"       .07464182522269779 2085225 2216475 2017 155645
1 2018 "Adana"        .0923363186226906 2085225 2220125 2018 192542
2 2003 "Adiyaman"                     0  590935       . 2003      .
2 2004 "Adiyaman"                     0  590935       . 2004      .
2 2005 "Adiyaman"                     0  590935       . 2005      .
2 2006 "Adiyaman"                     0  590935       . 2006      .
2 2007 "Adiyaman"                     0  590935       . 2007      .
2 2008 "Adiyaman"                     0  590935       . 2008      .
2 2009 "Adiyaman"                     0  590935       . 2009      .
2 2010 "Adiyaman"                     0  590935  590935 2010      0
2 2011 "Adiyaman"                     0  590935  593931 2011      0
2 2012 "Adiyaman"                     .  590935       . 2012      .
2 2013 "Adiyaman"    .01724893600819041  590935  597184 2013  10193
2 2014 "Adiyaman"   .056442755971468944  590935  597835 2014  33354
2 2015 "Adiyaman"    .03736620778935078  590935  602774 2015  22081
2 2016 "Adiyaman"    .04196231396008021  590935  610484 2016  24797
2 2017 "Adiyaman"    .04458019917588229  590935  615076 2017  26344
2 2018 "Adiyaman"   .049958117221014155  590935  624513 2018  29522
3 2003 "Afyon"                        0  697559       . 2003      .
3 2004 "Afyon"                        0  697559       . 2004      .
3 2005 "Afyon"                        0  697559       . 2005      .
3 2006 "Afyon"                        0  697559       . 2006      .
3 2007 "Afyon"                        0  697559       . 2007      .
3 2008 "Afyon"                        0  697559       . 2008      .
3 2009 "Afyon"                        0  697559       . 2009      .
3 2010 "Afyon"                        0  697559  697559 2010      0
3 2011 "Afyon"                        0  697559  698626 2011      0
3 2012 "Afyon"                        .  697559       . 2012      .
3 2013 "Afyon"                        0  697559  707123 2013      0
3 2014 "Afyon"     .0007167852468393355  697559  706371 2014    500
3 2015 "Afyon"      .004072773772541104  697559  709015 2015   2841
3 2016 "Afyon"     .0059937582340705235  697559  714523 2016   4181
3 2017 "Afyon"      .007070369674823205  697559  715693 2017   4932
3 2018 "Afyon"       .00851970944393234  697559  725568 2018   5943
4 2003 "Agri"                         0  542022       . 2003      .
4 2004 "Agri"                         0  542022       . 2004      .
4 2005 "Agri"                         0  542022       . 2005      .
4 2006 "Agri"                         0  542022       . 2006      .
4 2007 "Agri"                         0  542022       . 2007      .
4 2008 "Agri"                         0  542022       . 2008      .
4 2009 "Agri"                         0  542022       . 2009      .
4 2010 "Agri"                         0  542022  542022 2010      0
4 2011 "Agri"                         0  542022  555479 2011      0
4 2012 "Agri"                         .  542022       . 2012      .
4 2013 "Agri"                         0  542022  551177 2013      0
4 2014 "Agri"     .00018449435631764024  542022  549435 2014    100
4 2015 "Agri"      .0014685750762884163  542022  547210 2015    796
4 2016 "Agri"       .001581116633642177  542022  542255 2016    857
4 2017 "Agri"      .0018597031116818136  542022  536285 2017   1008
4 2018 "Agri"      .0020958558877683933  542022  539657 2018   1136
5 2003 "Amasya"                       0  334786       . 2003      .
5 2004 "Amasya"                       0  334786       . 2004      .
5 2005 "Amasya"                       0  334786       . 2005      .
5 2006 "Amasya"                       0  334786       . 2006      .
5 2007 "Amasya"                       0  334786       . 2007      .
5 2008 "Amasya"                       0  334786       . 2008      .
5 2009 "Amasya"                       0  334786       . 2009      .
5 2010 "Amasya"                       0  334786  334786 2010      0
5 2011 "Amasya"                       0  334786  323079 2011      0
5 2012 "Amasya"                       .  334786       . 2012      .
5 2013 "Amasya"                       0  334786  321977 2013      0
5 2014 "Amasya"    .0002986982729265859  334786  321913 2014    100
5 2015 "Amasya"   .00043012551301428373  334786  322167 2015    144
5 2016 "Amasya"    .0006481752522506914  334786  326351 2016    217
5 2017 "Amasya"    .0012007670571648754  334786  329888 2017    402
5 2018 "Amasya"      .00170258015568154  334786  337508 2018    570
6 2003 "Ankara"                       0 4771716       . 2003      .
6 2004 "Ankara"                       0 4771716       . 2004      .
6 2005 "Ankara"                       0 4771716       . 2005      .
6 2006 "Ankara"                       0 4771716       . 2006      .
6 2007 "Ankara"                       0 4771716       . 2007      .
6 2008 "Ankara"                       0 4771716       . 2008      .
6 2009 "Ankara"                       0 4771716       . 2009      .
6 2010 "Ankara"                       0 4771716 4771716 2010      0
6 2011 "Ankara"                       0 4771716 4890893 2011      0
6 2012 "Ankara"                       . 4771716       . 2012      .
6 2013 "Ankara"                       0 4771716 5045083 2013      0
6 2014 "Ankara"    .0062870464210359545 4771716 5150072 2014  30000
6 2015 "Ankara"     .011310815647871751 4771716 5270575 2015  53972
6 2016 "Ankara"       .0140706194584925 4771716 5346518 2016  67141
6 2017 "Ankara"     .016816801335201005 4771716 5445026 2017  80245
6 2018 "Ankara"      .02015396557548689 4771716 5503985 2018  96169
7 2003 "Antalya"                      0 1978333       . 2003      .
7 2004 "Antalya"                      0 1978333       . 2004      .
7 2005 "Antalya"                      0 1978333       . 2005      .
7 2006 "Antalya"                      0 1978333       . 2006      .
end
    Tags: None
  • #2
    Code:
    help joinby
    I'm suggesting this since you have multiple occurrences of "plate" in each file; however, it might not be correct since you haven't explained why you need data from one incorporated with data from another file
    • 1 like

    Comment

    • #3
      Dear Rich,

      Thank you very much. I want my individual level data look like this. I hope this is helpful.
      CASEID plate year_of_birth HAZ Refugee Population Initial_pop_2010 Ref_pop twin trend_1 trend_2 IV IV_sq
      1 1 2003 0 0 1 0
      2 1 2004 0 0 2 0
      3 1 2005 0 0 2 0
      4 1 2006 0 0 3 0
      5 1 2007 0 1 4 0
      6 1 2008 0 1 4 0
      7 1 2009 0 0 4 0
      8 1 2010 0 0 7 0
      9 1 2011 0 0 7 0
      10 1 2012 0,02 0 9 0
      11 1 2013 0,02 0 9 0
      12 1 2014 0,02 0 9 0
      13 1 2015 0,03 0 12 0
      14 1 2016 0,05 0 13 0
      15 1 2017 0 3
      16 1 2018 0 4

      Comment

      • #4
        Originally posted by Cansu Oymak View Post
        I am trying to merge and individual level data with another dataset at city level.

        . . . This is city level data:
        No, it is not.

        It is city-year_of_birth level data. If it were city level data, then you would be able to execute
        Code:
        isid plate
        or
        Code:
        isid city
        without error.

        Comment

        • #5
          Originally posted by Joseph Coveney View Post
          No, it is not.

          It is city-year_of_birth level data. If it were city level data, then you would be able to execute
          Code:
          isid plate
          or
          Code:
          isid city
          without error.
          Dear Joseph,

          Thank you for your reply. Bu still, I cannot figure out how can I merge them based on this information. I'd be really happy if you share some information. Thank you in advance.

          Comment

          • #6
            Do you have (or can you calculate) the year of birth for your individuals in your individual-level dataset? If so, then use the combination of plate and year_of_birth as the merging variable list.
            Code:
            use city_dataset
isid plate year_of_birth
merge 1:m plate year_of_birth using individual_dataset, keep(match)

            Comment

            • #7
              Originally posted by Joseph Coveney View Post
              Do you have (or can you calculate) the year of birth for your individuals in your individual-level dataset? If so, then use the combination of plate and year_of_birth as the merging variable list.
              Code:
              use city_dataset
isid plate year_of_birth
merge 1:m plate year_of_birth using individual_dataset, keep(match)
              Dear Joseph, I do have the year_of_birth in the individual dataset as well. However, for this time, it says "variable plate year_of_birth does not uniquely identify observations in the master data"

              My unique identifier in the individual level data is CASEID. It is bydefault as it is DHS data. But for city-level data, I created it by myself.

              Comment

              • #8
                Are you using the individual dataset as the master data in the merge? (You have yet to show anything at all about the commands that you use.)

                If so, then reverse the cardinality of the join, like so:
                Code:
                merge m:1 plate year_of_birth using city_dataset, keep(match)

                Comment

                • #9
                  Originally posted by Joseph Coveney View Post
                  Are you using the individual dataset as the master data in the merge? (You have yet to show anything at all about the commands that you use.)

                  If so, then reverse the cardinality of the join, like so:
                  Code:
                  merge m:1 plate year_of_birth using city_dataset, keep(match)
                  Dear Professor, tahnk you very much. It worked

                  Comment

                  Previous Next
                  Working...
                  X