Announcement

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

    variables do not uniquely identify observations in the using data

    Hello everyone,

    I am trying to merge two data-sets bit it says that "variables company_id Country do not uniquely identify observations in the using data".

    The datasets are:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str8 Country str15 company_id double market_return long date
"Italy"    "IT0004554116(P)" -.1088531769085596 20157
"Greece"   "GRS339003022(P)" -.1088531769085596 20157
"Greece"   "GRS224003004(P)" -.1088531769085596 20157
"Spain"    "ES0166287005(P)" -.1088531769085596 20157
"Spain"    "ES0115961031(P)" -.1088531769085596 20157
"Spain"    "ES0155174032(P)" -.1088531769085596 20157
"Italy"    "IT0005119810(P)" -.1088531769085596 20157
"Spain"    "ES0155728035(P)" -.1088531769085596 20157
"Greece"   "GRS315003004(P)" -.1088531769085596 20157
"Ireland"  "IE0005711209(P)" -.1088531769085596 20157
"Spain"    "ES0109216038(P)" -.1088531769085596 20157
"Greece"   "GRS297003006(P)" -.1088531769085596 20157
"Portugal" "PTEDP0AM0009(P)" -.1088531769085596 20157
"Greece"   "GRS491003000(P)" -.1088531769085596 20157
"Greece"   "GRS343313003(P)" -.1088531769085596 20157
"Spain"    "ES0173442031(P)" -.1088531769085596 20157
"Ireland"  "IE00B4Q65W67(P)" -.1088531769085596 20157
"Spain"    "ES0108852031(P)" -.1088531769085596 20157
"Italy"    "IT0001455473(P)" -.1088531769085596 20157
"Spain"    "ES0145834034(P)" -.1088531769085596 20157
"Spain"    "ES0126227034(P)" -.1088531769085596 20157
"Spain"    "ES0116012032(P)" -.1088531769085596 20157
"Spain"    "ES0142982034(P)" -.1088531769085596 20157
"Spain"    "ES0150481036(P)" -.1088531769085596 20157
"Spain"    "ES0126292038(P)" -.1088531769085596 20157
"Spain"    "ES0114816038(P)" -.1088531769085596 20157
"Italy"    "IT0000074135(P)" -.1088531769085596 20157
"Spain"    "ES0184822031(P)" -.1088531769085596 20157
"Greece"   "GRS351003009(P)" -.1088531769085596 20157
"Spain"    "ES0131491039(P)" -.1088531769085596 20157
"Spain"    "ES0123999031(P)" -.1088531769085596 20157
"Spain"    "ES0184674036(P)" -.1088531769085596 20157
"Greece"   "GRS120131008(P)" -.1088531769085596 20157
"Spain"    "ES0162772034(P)" -.1088531769085596 20157
"Italy"    "IT0004812258(P)" -.1088531769085596 20157
"Italy"    "IT0004007560(P)" -.1088531769085596 20157
"Italy"    "IT0005022097(P)" -.1088531769085596 20157
"Portugal" "PTS3P0AM0017(P)" -.1088531769085596 20157
"Spain"    "ES0140720030(P)" -.1088531769085596 20157
"Spain"    "ES0147562039(P)" -.1088531769085596 20157
"Greece"   "GRS500003009(P)" -.1088531769085596 20157
"Spain"    "ES0166337032(P)" -.1088531769085596 20157
"Greece"   "GRS359353000(P)" -.1088531769085596 20157
"Spain"    "ES0164976039(P)" -.1088531769085596 20157
"Italy"    "IT0001447785(P)" -.1088531769085596 20157
"Spain"    "ES0133473035(P)" -.1088531769085596 20157
"Greece"   "GRS015003007(P)" -.1088531769085596 20157
"Italy"    "IT0004764699(P)" -.1088531769085596 20157
"Italy"    "IT0003955009(P)" -.1088531769085596 20157
"Spain"    "ES0131421036(P)" -.1088531769085596 20157
"Spain"    "ES0116335037(P)" -.1088531769085596 20157
"Spain"    "ES0105097002(P)" -.1088531769085596 20157
"Spain"    "ES0119181032(P)" -.1088531769085596 20157
"Greece"   "GRS407183003(P)" -.1088531769085596 20157
"Ireland"  "IE00B1HDWM43(P)" -.1088531769085596 20157
"Spain"    "ES0108002033(P)" -.1088531769085596 20157
"Spain"    "ES0155585039(P)" -.1088531769085596 20157
"Spain"    "ES0176402032(P)" -.1088531769085596 20157
"Spain"    "ES0112818036(P)" -.1088531769085596 20157
"Italy"    "IT0004273915(P)" -.1088531769085596 20157
"Spain"    "ES0176404038(P)" -.1088531769085596 20157
"Spain"    "ES0155011036(P)" -.1088531769085596 20157
"Spain"    "ES0116494016(P)" -.1088531769085596 20157
"Spain"    "ES0155605035(P)" -.1088531769085596 20157
"Spain"    "ES0166417032(P)" -.1088531769085596 20157
"Italy"    "IT0000068525(P)" -.1088531769085596 20157
"Spain"    "ES0155524038(P)" -.1088531769085596 20157
"Spain"    "ES0140850035(P)" -.1088531769085596 20157
"Spain"    "ES0168761031(P)" -.1088531769085596 20157
"Italy"    "IT0004195308(P)" -.1088531769085596 20157
"Spain"    "ES0141269037(P)" -.1088531769085596 20157
end
    and

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str8 Country str15 company_id double delta_log_price long date float(SMP1 SMP2 LTRO1 LTRO2 OMT1 OMT2 OMT3 OMT4 APP) int year float date_seq
"Greece"   "GRS149001000(P)"  -1.791759469228055 18682 0 0 0 0 0 0 0 0 0 2011  809
"Spain"    "ES0181480114(P)" -1.6511921847816873 18191 0 0 0 0 0 0 0 0 0 2009  463
"Italy"    "IT0005187940(P)" -1.5040773967762742 20076 0 0 0 0 0 0 0 0 0 2014 1803
"Spain"    "ES0172821037(P)"  -1.482098490057499 18179 0 0 0 0 0 0 0 0 0 2009  455
"Portugal" "PTSCB0AM0001(P)" -1.4816045409242153 19277 0 0 0 0 0 0 0 0 0 2012 1234
"Greece"   "GRS065003006(P)" -1.4271163556401456 18464 0 0 0 0 0 0 0 0 0 2010  655
"Portugal" "PTCDU0AE0003(P)"  -1.330346287187358 18408 0 0 0 0 0 0 0 0 0 2010  615
"Portugal" "PTRED0AP0010(P)"  -1.286210902562908 20104 0 0 0 0 0 0 0 0 0 2015 1823
"Portugal" "PTFEN0AP0006(P)" -1.2809338454620642 18318 0 0 0 0 0 0 0 0 0 2010  551
"Italy"    "PTLIT0AE0005(P)" -1.2671642647383075 19491 0 0 0 0 0 0 0 0 0 2013 1386
"Ireland"  "IE0003073255(P)" -1.2367626271489267 18925 0 0 0 0 0 0 0 0 0 2011  982
"Portugal" "PTGPA0AP0007(P)" -1.2343575409526524 19198 0 0 0 0 0 0 0 0 0 2012 1177
"Spain"    "ES0109260531(P)"  -1.223940768703466 20256 0 0 0 0 0 0 0 0 0 2015 1929
"Italy"    "PTLIT0AE0005(P)" -1.2211182112809245 19247 0 0 0 0 0 0 0 0 0 2012 1212
"Portugal" "PTCUR0AP0000(P)"  -1.205222023726368 19478 0 0 0 0 0 0 0 0 0 2013 1377
"Italy"    "IT0003056386(P)"  -1.165009874612094 20436 0 0 0 0 0 0 0 0 0 2015 2057
"Ireland"  "IE00B66ML191(P)"  -1.139434283188365 18941 0 0 0 0 0 0 0 0 0 2011  994
"Portugal" "PTCUR0AP0000(P)" -1.1394342831883648 18486 0 0 0 0 0 0 0 0 0 2010  670
"Portugal" "PTGPA0AP0007(P)" -1.1332614240812837 19219 0 0 0 0 0 0 0 0 0 2012 1192
"Portugal" "PTCDU0AE0003(P)"  -1.126648720891393 18443 0 0 0 0 0 0 0 0 0 2010  640
"Italy"    "IT0005187940(P)"  -1.123305172261155 19236 0 0 0 0 0 0 0 0 0 2012 1205
"Greece"   "GRS446003014(P)" -1.0986122886681098 18634 0 0 0 0 0 0 0 0 0 2011  775
"Ireland"  "IE00B66ML191(P)" -1.0986122886681096 18682 0 0 0 0 0 0 0 0 0 2011  809
"Spain"    "ES0176406066(P)" -1.0986122886681096 19507 0 0 0 0 0 0 0 0 0 2013 1398
"Spain"    "ES0116162068(P)"  -1.083686638451434 20093 0 0 0 0 0 0 0 0 0 2015 1814
"Italy"    "PTLIT0AE0005(P)" -1.0603423124074791 19333 0 0 0 0 0 0 0 0 0 2012 1274
"Italy"    "IT0004659428(P)" -1.0553451866429482 19310 0 0 0 0 0 0 0 0 0 2012 1257
"Portugal" "PTSCB0AM0001(P)" -1.0498221244986776 18872 0 0 0 0 0 0 0 0 0 2011  945
"Portugal" "PTCUR0AP0000(P)"  -1.043042437513299 18003 0 0 0 0 0 0 0 0 0 2009  332
"Portugal" "PTSCT0AP0018(P)" -1.0316728059930003 19036 0 0 0 0 0 0 0 0 0 2012 1061
"Portugal" "PTGMC0AM0003(P)" -1.0296194171811583 18806 0 0 0 0 0 0 0 0 0 2011  897
"Portugal" "PTSNG0AE0007(P)" -1.0296194171811583 17832 0 0 0 0 0 0 0 0 0 2008  213
"Italy"    "IT0005013013(P)" -1.0282660080759063 19402 0 0 0 0 0 0 0 0 0 2013 1323
"Portugal" "PTRED0AP0010(P)" -1.0055218656020977 19879 0 0 0 0 0 0 0 0 0 2014 1663
"Greece"   "GRS243003019(P)"  -.9808292530117264 18388 0 0 0 0 0 0 0 0 0 2010  601
"Portugal" "PTCPN0AE0002(P)"  -.9734491457141036 18451 0 0 0 0 0 0 0 0 0 2010  646
"Spain"    "ES0126962002(P)"  -.9725060902121332 19904 0 0 0 0 0 0 0 0 0 2014 1680
"Portugal" "PTCOM0AE0007(P)"  -.9555114450274362 19127 0 0 0 0 0 0 0 0 0 2012 1126
"Ireland"  "IE00BH3XCL94(P)"  -.9339030451312738 18560 0 0 0 0 0 0 0 0 0 2010  722
"Portugal" "PTSNG0AE0007(P)"  -.9252594018569155 18436 0 0 0 0 0 0 0 0 0 2010  635
"Greece"   "GRS276003019(P)"  -.9218309122497705 18443 0 0 0 0 0 0 0 0 0 2010  640
"Spain"    "ES0169350016(P)"  -.9162907318741553 19421 0 0 0 0 0 0 0 0 0 2013 1336
"Portugal" "PTFEN0AP0006(P)"  -.9162907318741551 18350 0 0 0 0 0 0 0 0 0 2010  573
"Portugal" "PTFEN0AP0006(P)"  -.9162907318741551 19414 0 0 0 0 0 0 0 0 0 2013 1331
"Ireland"  "IE00B4XVDC01(P)"  -.9162907318741549 17813 0 0 0 0 0 0 0 0 0 2008  200
"Portugal" "PTGPA0AP0007(P)"  -.8987464222232456 19922 0 0 0 0 0 0 0 0 0 2014 1694
"Portugal" "PTRED0AP0010(P)"  -.8895204908095502 19519 0 0 0 0 0 0 0 0 0 2013 1407
"Italy"    "IT0004827447(P)"  -.8873031950009029 19201 0 0 0 0 0 0 0 0 0 2012 1180
"Ireland"  "IE00BWB8X525(P)"   -.885917763853322 18718 0 0 0 0 0 0 0 0 0 2011  835
"Ireland"  "IE00BYSZ9G33(P)"  -.8823864215739796 17916 0 0 0 0 0 0 0 0 0 2009  269
"Italy"    "IT0004147952(P)"  -.8819143361326565 18388 0 0 0 0 0 0 0 0 0 2010  601
"Spain"    "ES0143421073(P)"   -.876016832736215 19656 0 0 0 0 0 0 0 0 0 2013 1505
"Portugal" "PTFEN0AP0006(P)"  -.8690378470236095 17541 0 0 0 0 0 0 0 0 0 2008    8
"Ireland"  "IE00B66ML191(P)"  -.8574502318512216 18931 0 0 0 0 0 0 0 0 0 2011  986
"Italy"    "PTLIT0AE0005(P)"  -.8481076274677047 17855 0 0 0 0 0 0 0 0 0 2008  230
"Portugal" "PTCPA0AP0006(P)"   -.841647188878389 18548 0 0 0 0 0 0 0 0 0 2010  714
"Portugal" "PTGMC0AM0003(P)"  -.8163094653360649 18823 0 0 0 0 0 0 0 0 0 2011  910
"Ireland"  "IE00B4Q65W67(P)"  -.8109302162163287 20376 0 0 0 0 0 0 0 0 0 2015 2015
"Portugal" "PTSCT0AP0018(P)"  -.8059426747052896 19323 0 0 0 0 0 0 0 0 0 2012 1266
"Ireland"  "IE0030606259(P)"  -.7931193734700817 17916 0 0 0 0 0 0 0 0 0 2009  269
"Spain"    "ES0133534034(P)"   -.791679704720742 17853 0 0 0 0 0 0 0 0 0 2008  228
"Italy"    "PTLIT0AE0005(P)"  -.7729731800302281 18435 0 0 0 0 0 0 0 0 0 2010  634
"Portugal" "PTS3P0AM0017(P)"  -.7718627480254194 20030 0 0 0 0 0 0 0 0 0 2014 1769
"Ireland"  "IE00B4Q65W67(P)"  -.7503055943998939 19449 0 0 0 0 0 0 0 0 0 2013 1356
"Portugal" "PTS3P0AM0017(P)"  -.7398648051274046 20059 0 0 0 0 0 0 0 0 0 2014 1790
"Spain"    "ES0105200416(P)"  -.7353626990872919 20417 0 0 0 0 0 0 0 0 0 2015 2044
"Italy"    "PTLIT0AE0005(P)"  -.7288545056569359 17729 0 0 0 0 0 0 0 0 0 2008  141
"Portugal" "PTSNG0AE0007(P)"  -.7248958788745256 18904 0 0 0 0 0 0 0 0 0 2011  967
"Spain"    "ES0113307021(P)"  -.7221347174331978 19501 0 0 0 0 0 0 0 0 0 2013 1394
"Portugal" "PTGVE1AE0009(P)"  -.7221347174331976 20446 0 0 0 0 0 0 0 0 0 2015 2065
"Ireland"  "IE00B59HWB19(P)"  -.7205461547480598 19346 0 0 0 0 0 0 0 0 0 2012 1283
"Portugal" "PTSNG0AE0007(P)"  -.7200546334798696 18046 0 0 0 0 0 0 0 0 0 2009  363
"Italy"    "IT0004513666(P)"  -.7107687819097649 18198 0 0 0 0 0 0 0 0 0 2009  467
"Portugal" "PTGMC0AM0003(P)"  -.7090205297162355 19907 0 0 0 0 0 0 0 0 0 2014 1683
"Italy"    "PTLIT0AE0005(P)"  -.7043538723695069 18675 0 0 0 0 0 0 0 0 0 2011  804
"Greece"   "GRS510003015(P)"  -.6967378486906738 18688 0 0 0 0 0 0 0 0 0 2011  813
"Italy"    "IT0001424644(P)"  -.6931471805599454 18605 0 0 0 0 0 0 0 0 0 2010  755
"Spain"    "ES0176406066(P)"  -.6931471805599454 20058 0 0 0 0 0 0 0 0 0 2014 1789
"Spain"    "ES0176406066(P)"  -.6931471805599454 19827 0 0 0 0 0 0 0 0 0 2014 1625
"Spain"    "ES0176406066(P)"  -.6931471805599454 20104 0 0 0 0 0 0 0 0 0 2015 1823
"Spain"    "ES0176406066(P)"  -.6931471805599454 20060 0 0 0 0 0 0 0 0 0 2014 1791
"Portugal" "PTLIG0AE0002(P)"  -.6931471805599454 19025 0 0 0 0 0 0 0 0 0 2012 1054
end

    Could you please help me with that? Why do I get this error message? (the dates are the same for both of my datasets, in the first I got the same days because I sort for market_return)) Thank you in advance
    Tags: None
  • #2
    Well, the message means exactly what it says. Although in your first data set there is just one observation for each combination of company_id and Country, in the second data set there are many. This may or may not be a problem. It appears that each of the observations for a given company_id and Country are distinguished by a different date in the second data set, whereas in the first data set there is only one date. So perhaps it is your intention to have a single master record that is about the company_id and Country itself, and you want to pair each of those with several records from the second data set. Then it's just a matter of using -merge 1:m- to do that.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str8 Country str15 company_id double market_return long date
"Italy"    "IT0004554116(P)" -.1088531769085596 20157
"Greece"   "GRS339003022(P)" -.1088531769085596 20157
"Greece"   "GRS224003004(P)" -.1088531769085596 20157
"Spain"    "ES0166287005(P)" -.1088531769085596 20157
"Spain"    "ES0115961031(P)" -.1088531769085596 20157
"Spain"    "ES0155174032(P)" -.1088531769085596 20157
"Italy"    "IT0005119810(P)" -.1088531769085596 20157
"Spain"    "ES0155728035(P)" -.1088531769085596 20157
"Greece"   "GRS315003004(P)" -.1088531769085596 20157
"Ireland"  "IE0005711209(P)" -.1088531769085596 20157
"Spain"    "ES0109216038(P)" -.1088531769085596 20157
"Greece"   "GRS297003006(P)" -.1088531769085596 20157
"Portugal" "PTEDP0AM0009(P)" -.1088531769085596 20157
"Greece"   "GRS491003000(P)" -.1088531769085596 20157
"Greece"   "GRS343313003(P)" -.1088531769085596 20157
"Spain"    "ES0173442031(P)" -.1088531769085596 20157
"Ireland"  "IE00B4Q65W67(P)" -.1088531769085596 20157
"Spain"    "ES0108852031(P)" -.1088531769085596 20157
"Italy"    "IT0001455473(P)" -.1088531769085596 20157
"Spain"    "ES0145834034(P)" -.1088531769085596 20157
"Spain"    "ES0126227034(P)" -.1088531769085596 20157
"Spain"    "ES0116012032(P)" -.1088531769085596 20157
"Spain"    "ES0142982034(P)" -.1088531769085596 20157
"Spain"    "ES0150481036(P)" -.1088531769085596 20157
"Spain"    "ES0126292038(P)" -.1088531769085596 20157
"Spain"    "ES0114816038(P)" -.1088531769085596 20157
"Italy"    "IT0000074135(P)" -.1088531769085596 20157
"Spain"    "ES0184822031(P)" -.1088531769085596 20157
"Greece"   "GRS351003009(P)" -.1088531769085596 20157
"Spain"    "ES0131491039(P)" -.1088531769085596 20157
"Spain"    "ES0123999031(P)" -.1088531769085596 20157
"Spain"    "ES0184674036(P)" -.1088531769085596 20157
"Greece"   "GRS120131008(P)" -.1088531769085596 20157
"Spain"    "ES0162772034(P)" -.1088531769085596 20157
"Italy"    "IT0004812258(P)" -.1088531769085596 20157
"Italy"    "IT0004007560(P)" -.1088531769085596 20157
"Italy"    "IT0005022097(P)" -.1088531769085596 20157
"Portugal" "PTS3P0AM0017(P)" -.1088531769085596 20157
"Spain"    "ES0140720030(P)" -.1088531769085596 20157
"Spain"    "ES0147562039(P)" -.1088531769085596 20157
"Greece"   "GRS500003009(P)" -.1088531769085596 20157
"Spain"    "ES0166337032(P)" -.1088531769085596 20157
"Greece"   "GRS359353000(P)" -.1088531769085596 20157
"Spain"    "ES0164976039(P)" -.1088531769085596 20157
"Italy"    "IT0001447785(P)" -.1088531769085596 20157
"Spain"    "ES0133473035(P)" -.1088531769085596 20157
"Greece"   "GRS015003007(P)" -.1088531769085596 20157
"Italy"    "IT0004764699(P)" -.1088531769085596 20157
"Italy"    "IT0003955009(P)" -.1088531769085596 20157
"Spain"    "ES0131421036(P)" -.1088531769085596 20157
"Spain"    "ES0116335037(P)" -.1088531769085596 20157
"Spain"    "ES0105097002(P)" -.1088531769085596 20157
"Spain"    "ES0119181032(P)" -.1088531769085596 20157
"Greece"   "GRS407183003(P)" -.1088531769085596 20157
"Ireland"  "IE00B1HDWM43(P)" -.1088531769085596 20157
"Spain"    "ES0108002033(P)" -.1088531769085596 20157
"Spain"    "ES0155585039(P)" -.1088531769085596 20157
"Spain"    "ES0176402032(P)" -.1088531769085596 20157
"Spain"    "ES0112818036(P)" -.1088531769085596 20157
"Italy"    "IT0004273915(P)" -.1088531769085596 20157
"Spain"    "ES0176404038(P)" -.1088531769085596 20157
"Spain"    "ES0155011036(P)" -.1088531769085596 20157
"Spain"    "ES0116494016(P)" -.1088531769085596 20157
"Spain"    "ES0155605035(P)" -.1088531769085596 20157
"Spain"    "ES0166417032(P)" -.1088531769085596 20157
"Italy"    "IT0000068525(P)" -.1088531769085596 20157
"Spain"    "ES0155524038(P)" -.1088531769085596 20157
"Spain"    "ES0140850035(P)" -.1088531769085596 20157
"Spain"    "ES0168761031(P)" -.1088531769085596 20157
"Italy"    "IT0004195308(P)" -.1088531769085596 20157
"Spain"    "ES0141269037(P)" -.1088531769085596 20157
end
isid company_id Country
tempfile master
save `master'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 Country str15 company_id double delta_log_price long date float(SMP1 SMP2 LTRO1 LTRO2 OMT1 OMT2 OMT3 OMT4 APP) int year float date_seq
"Greece"   "GRS149001000(P)"  -1.791759469228055 18682 0 0 0 0 0 0 0 0 0 2011  809
"Spain"    "ES0181480114(P)" -1.6511921847816873 18191 0 0 0 0 0 0 0 0 0 2009  463
"Italy"    "IT0005187940(P)" -1.5040773967762742 20076 0 0 0 0 0 0 0 0 0 2014 1803
"Spain"    "ES0172821037(P)"  -1.482098490057499 18179 0 0 0 0 0 0 0 0 0 2009  455
"Portugal" "PTSCB0AM0001(P)" -1.4816045409242153 19277 0 0 0 0 0 0 0 0 0 2012 1234
"Greece"   "GRS065003006(P)" -1.4271163556401456 18464 0 0 0 0 0 0 0 0 0 2010  655
"Portugal" "PTCDU0AE0003(P)"  -1.330346287187358 18408 0 0 0 0 0 0 0 0 0 2010  615
"Portugal" "PTRED0AP0010(P)"  -1.286210902562908 20104 0 0 0 0 0 0 0 0 0 2015 1823
"Portugal" "PTFEN0AP0006(P)" -1.2809338454620642 18318 0 0 0 0 0 0 0 0 0 2010  551
"Italy"    "PTLIT0AE0005(P)" -1.2671642647383075 19491 0 0 0 0 0 0 0 0 0 2013 1386
"Ireland"  "IE0003073255(P)" -1.2367626271489267 18925 0 0 0 0 0 0 0 0 0 2011  982
"Portugal" "PTGPA0AP0007(P)" -1.2343575409526524 19198 0 0 0 0 0 0 0 0 0 2012 1177
"Spain"    "ES0109260531(P)"  -1.223940768703466 20256 0 0 0 0 0 0 0 0 0 2015 1929
"Italy"    "PTLIT0AE0005(P)" -1.2211182112809245 19247 0 0 0 0 0 0 0 0 0 2012 1212
"Portugal" "PTCUR0AP0000(P)"  -1.205222023726368 19478 0 0 0 0 0 0 0 0 0 2013 1377
"Italy"    "IT0003056386(P)"  -1.165009874612094 20436 0 0 0 0 0 0 0 0 0 2015 2057
"Ireland"  "IE00B66ML191(P)"  -1.139434283188365 18941 0 0 0 0 0 0 0 0 0 2011  994
"Portugal" "PTCUR0AP0000(P)" -1.1394342831883648 18486 0 0 0 0 0 0 0 0 0 2010  670
"Portugal" "PTGPA0AP0007(P)" -1.1332614240812837 19219 0 0 0 0 0 0 0 0 0 2012 1192
"Portugal" "PTCDU0AE0003(P)"  -1.126648720891393 18443 0 0 0 0 0 0 0 0 0 2010  640
"Italy"    "IT0005187940(P)"  -1.123305172261155 19236 0 0 0 0 0 0 0 0 0 2012 1205
"Greece"   "GRS446003014(P)" -1.0986122886681098 18634 0 0 0 0 0 0 0 0 0 2011  775
"Ireland"  "IE00B66ML191(P)" -1.0986122886681096 18682 0 0 0 0 0 0 0 0 0 2011  809
"Spain"    "ES0176406066(P)" -1.0986122886681096 19507 0 0 0 0 0 0 0 0 0 2013 1398
"Spain"    "ES0116162068(P)"  -1.083686638451434 20093 0 0 0 0 0 0 0 0 0 2015 1814
"Italy"    "PTLIT0AE0005(P)" -1.0603423124074791 19333 0 0 0 0 0 0 0 0 0 2012 1274
"Italy"    "IT0004659428(P)" -1.0553451866429482 19310 0 0 0 0 0 0 0 0 0 2012 1257
"Portugal" "PTSCB0AM0001(P)" -1.0498221244986776 18872 0 0 0 0 0 0 0 0 0 2011  945
"Portugal" "PTCUR0AP0000(P)"  -1.043042437513299 18003 0 0 0 0 0 0 0 0 0 2009  332
"Portugal" "PTSCT0AP0018(P)" -1.0316728059930003 19036 0 0 0 0 0 0 0 0 0 2012 1061
"Portugal" "PTGMC0AM0003(P)" -1.0296194171811583 18806 0 0 0 0 0 0 0 0 0 2011  897
"Portugal" "PTSNG0AE0007(P)" -1.0296194171811583 17832 0 0 0 0 0 0 0 0 0 2008  213
"Italy"    "IT0005013013(P)" -1.0282660080759063 19402 0 0 0 0 0 0 0 0 0 2013 1323
"Portugal" "PTRED0AP0010(P)" -1.0055218656020977 19879 0 0 0 0 0 0 0 0 0 2014 1663
"Greece"   "GRS243003019(P)"  -.9808292530117264 18388 0 0 0 0 0 0 0 0 0 2010  601
"Portugal" "PTCPN0AE0002(P)"  -.9734491457141036 18451 0 0 0 0 0 0 0 0 0 2010  646
"Spain"    "ES0126962002(P)"  -.9725060902121332 19904 0 0 0 0 0 0 0 0 0 2014 1680
"Portugal" "PTCOM0AE0007(P)"  -.9555114450274362 19127 0 0 0 0 0 0 0 0 0 2012 1126
"Ireland"  "IE00BH3XCL94(P)"  -.9339030451312738 18560 0 0 0 0 0 0 0 0 0 2010  722
"Portugal" "PTSNG0AE0007(P)"  -.9252594018569155 18436 0 0 0 0 0 0 0 0 0 2010  635
"Greece"   "GRS276003019(P)"  -.9218309122497705 18443 0 0 0 0 0 0 0 0 0 2010  640
"Spain"    "ES0169350016(P)"  -.9162907318741553 19421 0 0 0 0 0 0 0 0 0 2013 1336
"Portugal" "PTFEN0AP0006(P)"  -.9162907318741551 18350 0 0 0 0 0 0 0 0 0 2010  573
"Portugal" "PTFEN0AP0006(P)"  -.9162907318741551 19414 0 0 0 0 0 0 0 0 0 2013 1331
"Ireland"  "IE00B4XVDC01(P)"  -.9162907318741549 17813 0 0 0 0 0 0 0 0 0 2008  200
"Portugal" "PTGPA0AP0007(P)"  -.8987464222232456 19922 0 0 0 0 0 0 0 0 0 2014 1694
"Portugal" "PTRED0AP0010(P)"  -.8895204908095502 19519 0 0 0 0 0 0 0 0 0 2013 1407
"Italy"    "IT0004827447(P)"  -.8873031950009029 19201 0 0 0 0 0 0 0 0 0 2012 1180
"Ireland"  "IE00BWB8X525(P)"   -.885917763853322 18718 0 0 0 0 0 0 0 0 0 2011  835
"Ireland"  "IE00BYSZ9G33(P)"  -.8823864215739796 17916 0 0 0 0 0 0 0 0 0 2009  269
"Italy"    "IT0004147952(P)"  -.8819143361326565 18388 0 0 0 0 0 0 0 0 0 2010  601
"Spain"    "ES0143421073(P)"   -.876016832736215 19656 0 0 0 0 0 0 0 0 0 2013 1505
"Portugal" "PTFEN0AP0006(P)"  -.8690378470236095 17541 0 0 0 0 0 0 0 0 0 2008    8
"Ireland"  "IE00B66ML191(P)"  -.8574502318512216 18931 0 0 0 0 0 0 0 0 0 2011  986
"Italy"    "PTLIT0AE0005(P)"  -.8481076274677047 17855 0 0 0 0 0 0 0 0 0 2008  230
"Portugal" "PTCPA0AP0006(P)"   -.841647188878389 18548 0 0 0 0 0 0 0 0 0 2010  714
"Portugal" "PTGMC0AM0003(P)"  -.8163094653360649 18823 0 0 0 0 0 0 0 0 0 2011  910
"Ireland"  "IE00B4Q65W67(P)"  -.8109302162163287 20376 0 0 0 0 0 0 0 0 0 2015 2015
"Portugal" "PTSCT0AP0018(P)"  -.8059426747052896 19323 0 0 0 0 0 0 0 0 0 2012 1266
"Ireland"  "IE0030606259(P)"  -.7931193734700817 17916 0 0 0 0 0 0 0 0 0 2009  269
"Spain"    "ES0133534034(P)"   -.791679704720742 17853 0 0 0 0 0 0 0 0 0 2008  228
"Italy"    "PTLIT0AE0005(P)"  -.7729731800302281 18435 0 0 0 0 0 0 0 0 0 2010  634
"Portugal" "PTS3P0AM0017(P)"  -.7718627480254194 20030 0 0 0 0 0 0 0 0 0 2014 1769
"Ireland"  "IE00B4Q65W67(P)"  -.7503055943998939 19449 0 0 0 0 0 0 0 0 0 2013 1356
"Portugal" "PTS3P0AM0017(P)"  -.7398648051274046 20059 0 0 0 0 0 0 0 0 0 2014 1790
"Spain"    "ES0105200416(P)"  -.7353626990872919 20417 0 0 0 0 0 0 0 0 0 2015 2044
"Italy"    "PTLIT0AE0005(P)"  -.7288545056569359 17729 0 0 0 0 0 0 0 0 0 2008  141
"Portugal" "PTSNG0AE0007(P)"  -.7248958788745256 18904 0 0 0 0 0 0 0 0 0 2011  967
"Spain"    "ES0113307021(P)"  -.7221347174331978 19501 0 0 0 0 0 0 0 0 0 2013 1394
"Portugal" "PTGVE1AE0009(P)"  -.7221347174331976 20446 0 0 0 0 0 0 0 0 0 2015 2065
"Ireland"  "IE00B59HWB19(P)"  -.7205461547480598 19346 0 0 0 0 0 0 0 0 0 2012 1283
"Portugal" "PTSNG0AE0007(P)"  -.7200546334798696 18046 0 0 0 0 0 0 0 0 0 2009  363
"Italy"    "IT0004513666(P)"  -.7107687819097649 18198 0 0 0 0 0 0 0 0 0 2009  467
"Portugal" "PTGMC0AM0003(P)"  -.7090205297162355 19907 0 0 0 0 0 0 0 0 0 2014 1683
"Italy"    "PTLIT0AE0005(P)"  -.7043538723695069 18675 0 0 0 0 0 0 0 0 0 2011  804
"Greece"   "GRS510003015(P)"  -.6967378486906738 18688 0 0 0 0 0 0 0 0 0 2011  813
"Italy"    "IT0001424644(P)"  -.6931471805599454 18605 0 0 0 0 0 0 0 0 0 2010  755
"Spain"    "ES0176406066(P)"  -.6931471805599454 20058 0 0 0 0 0 0 0 0 0 2014 1789
"Spain"    "ES0176406066(P)"  -.6931471805599454 19827 0 0 0 0 0 0 0 0 0 2014 1625
"Spain"    "ES0176406066(P)"  -.6931471805599454 20104 0 0 0 0 0 0 0 0 0 2015 1823
"Spain"    "ES0176406066(P)"  -.6931471805599454 20060 0 0 0 0 0 0 0 0 0 2014 1791
"Portugal" "PTLIG0AE0002(P)"  -.6931471805599454 19025 0 0 0 0 0 0 0 0 0 2012 1054
end
duplicates tag company_id Country, gen(flag)
sort company_id Country date
list company_id Country date if flag, noobs sepby(company_id)
drop flag
tempfile using_data
save `using_data'

use `master'
merge 1:m company_id Country using `using_data'
    Now, in your example data, there is little overlap of company_id Country pairings between the two data sets, so you only get 4 matched observations, but presumably that is not the case in your full data.
    • 1 like

    Comment

    • #3
      Deat Clyde,

      The first data-set doesn't include only one date, I sorted by market return and for this reason it seems like that. In the first data-set I have 5 countries and the date is daily from 2008-2015 and many companies id (as the second data-set).

      Comment

      • #4
        Lia, the message says the "variables company_id Country do not uniquely identify observations in the using data". This tells me that you are merging on these two variables. Checking for uniqueness in the second dataset:

        Code:
        . duplicates list company_id Country

Duplicates in terms of company_id Country

  +--------------------------------------------+
  | group:   obs:        company_id    Country |
  |--------------------------------------------|
  |      1     24   ES0176406066(P)      Spain |
  |      1     78   ES0176406066(P)      Spain |
  |      1     79   ES0176406066(P)      Spain |
  |      1     80   ES0176406066(P)      Spain |
  |      1     81   ES0176406066(P)      Spain |
  |--------------------------------------------|
  |      2     58   IE00B4Q65W67(P)    Ireland |
  |      2     64   IE00B4Q65W67(P)    Ireland |
  |      3     17   IE00B66ML191(P)    Ireland |
  |      3     23   IE00B66ML191(P)    Ireland |
  |      3     54   IE00B66ML191(P)    Ireland |
  |--------------------------------------------|
  |      4      3   IT0005187940(P)      Italy |
  |      4     21   IT0005187940(P)      Italy |
  |      5      7   PTCDU0AE0003(P)   Portugal |
  |      5     20   PTCDU0AE0003(P)   Portugal |
  |      6     15   PTCUR0AP0000(P)   Portugal |
  |--------------------------------------------|
  |      6     18   PTCUR0AP0000(P)   Portugal |
  |      6     29   PTCUR0AP0000(P)   Portugal |
  |      7      9   PTFEN0AP0006(P)   Portugal |
  |      7     43   PTFEN0AP0006(P)   Portugal |
  |      7     44   PTFEN0AP0006(P)   Portugal |
  |--------------------------------------------|
  |      7     53   PTFEN0AP0006(P)   Portugal |
  |      8     31   PTGMC0AM0003(P)   Portugal |
  |      8     57   PTGMC0AM0003(P)   Portugal |
  |      8     74   PTGMC0AM0003(P)   Portugal |
  |      9     12   PTGPA0AP0007(P)   Portugal |
  |--------------------------------------------|
  |      9     19   PTGPA0AP0007(P)   Portugal |
  |      9     46   PTGPA0AP0007(P)   Portugal |
  |     10     10   PTLIT0AE0005(P)      Italy |
  |     10     14   PTLIT0AE0005(P)      Italy |
  |     10     26   PTLIT0AE0005(P)      Italy |
  |--------------------------------------------|
  |     10     55   PTLIT0AE0005(P)      Italy |
  |     10     62   PTLIT0AE0005(P)      Italy |
  |     10     67   PTLIT0AE0005(P)      Italy |
  |     10     75   PTLIT0AE0005(P)      Italy |
  |     11      8   PTRED0AP0010(P)   Portugal |
  |--------------------------------------------|
  |     11     34   PTRED0AP0010(P)   Portugal |
  |     11     47   PTRED0AP0010(P)   Portugal |
  |     12     63   PTS3P0AM0017(P)   Portugal |
  |     12     65   PTS3P0AM0017(P)   Portugal |
  |     13      5   PTSCB0AM0001(P)   Portugal |
  |--------------------------------------------|
  |     13     28   PTSCB0AM0001(P)   Portugal |
  |     14     30   PTSCT0AP0018(P)   Portugal |
  |     14     59   PTSCT0AP0018(P)   Portugal |
  |     15     32   PTSNG0AE0007(P)   Portugal |
  |     15     40   PTSNG0AE0007(P)   Portugal |
  |--------------------------------------------|
  |     15     68   PTSNG0AE0007(P)   Portugal |
  |     15     72   PTSNG0AE0007(P)   Portugal |
  +--------------------------------------------+
        Either you need to include more variables to ensure a 1:1 merge, or permit a 1:m merge, depending on what you want to do.

        Best, Sergiy

        Comment

        • #5
          You may want to use the duplicates command to identify the duplicate observations. Often, these appear when you have missing data on one of the merging variables. Alternatively, if you really should have duplicates, the 1:m might be appropriate.

          Comment

          Previous Next
          Working...
          X