Announcement

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

    Select one observation within group with complex criteria

    Hello,
    I have to select only one document per each firmid (sometimes I have one document per firm id, sometimes many, and the same document can be associated to different firm id, so neither documentid or firmid are unique) according to the following criteria: if there is more than one document, I have to take, among type ==1 or type==6, the one with the smaller datetake. If no type ==1 or type==6 document exists, I have to take a document for which the variable amel==1. Here an example.

    Any hint?

    Thank you

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str12 documentid float(type amel datetake) str14 firmid
"a07512000384" 1 1 1838 "33919668500012"
"a00113000548" 1 1 1282 "45060988800018"
"a00115000892" 1 1  731 "32343851500021"
"a00115001035" 1 0  668 "77554456200108"
"a00116002086" 6 1  221 "55208131715412"
"a00212000444" 1 1 1648 "58558085500104"
"a00216001069" 1 0  476 "39451475600029"
"a00312000086" 1 1 1851 "41873471100017"
"a00313000308" 1 0 1209 "38289442600016"
"a00414000040" 1 0 1044 "30582329600077"
"a00716000762" 6 0  206 "55208131715461"
"a00815000275" 1 1  680 "77734395500061"
"a01014000094" 1 0 1004 "56288017900030"
"a01015000317" 1 1  646 "56288017900030"
"a01314000477" 1 0 1130 "30233237400016"
"a01314001232" 1 1 1018 "07280174900010"
"a01414000197" 6 1 1058 "50453874500028"
"a01415000895" 1 0  681 "30634863200093"
"a01415001076" 1 0  915 "39821962600015"
"a01415001102" 1 1  654 "54201695101419"
"a01416001972" 1 0  257 "64382064000016"
"a01416002249" 1 0  382 "39821962600015"
"a01416002493" 1 0  156 "57206768400660"
"a01616001032" 6 1  298 "90572051200011"
"a01616001086" 6 1  268 "60182032700034"
"a01616001312" 1 1  206 "41317505000058"
"a01715001072" 6 1  649 "31851878400018"
"a01814000022" 6 0 1136 "32246383700011"
"a01814000107" 1 0 1019 "51772011600022"
"a01815000539" 6 1  388 "32246383700011"
"a01816000777" 6 1   17 "32246383700011"
"a01817000807" 6 1   19 "38211923800030"
"a02213000201" 1 1 1453 "77745617900020"
"a02516001917" 1 1  180 "44471927200148"
"a02614000281" 1 1 1023 "77945636700019"
"a02813000099" 1 1 1443 "31439767000048"
"a02813000447" 6 1 1207 "34888467700132"
"a02814000161" 1 0 1047 "31439767000048"
"a02814000458" 1 1  779 "77567898000092"
"a02914001078" 1 1 1072 "34757035000038"
"a02915001759" 1 1  599 "77622146700017"
"a02915002054" 1 1  599 "54695037900034"
"a02916003431" 6 1   65 "80261109500026"
"a02917003541" 1 1   67 "39931174500026"
"a03113000810" 1 1 1283 "55201683400053"
"a03312000044" 1 1 1843 "89685028600015"
"a03312000056" 1 0 1842 "52558019700057"
"a03312000127" 1 1 1836 "48770604600025"
"a03312000266" 1 1 1835 "43465124600010"
"a03312000837" 1 1 1719 "47020299500028"
"a03312001050" 1 1 1649 "31922755900014"
"a03312001404" 1 1 1626 "43331347500025"
"a03313000321" 1 1 1496 "78183171400014"
"a03313000863" 1 1 1438 "45620494000542"
"a03313001750" 1 0 1153 "45520353900016"
"a03314000360" 1 0 1041 "77811582401699"
"a03314000478" 1 0 1023 "42405982200256"
"a03314000824" 1 0  991 "30352750100033"
"a03315001901" 1 0  750 "45520353900016"
"a03315001977" 1 1  744 "46320174900033"
"a03315002487" 1 0  641 "30352750100033"
"a03316003901" 1 0  327 "77811582401699"
"a03316004007" 1 0  302 "42405982200256"
"a03316004039" 1 0  316 "52558019700057"
"a03316004098" 1 0  298 "48770604600025"
"a03316004315" 1 0  261 "43331347500025"
"a03513001217" 1 0 1249 "32029220400015"
"a03517005779" 6 1   17 "39939393300052"
"a03716002686" 6 1  225 "55208131724224"
"a04416004299" 6 1  379 "33890331300025"
"a04416005141" 6 0  253 "55208131736210"
"a04517002780" 1 1   22 "08628039300041"
"a04715000316" 1 1  793 "38067825000016"
"a04716000720" 6 0  376 "72705008000017"
"a04914000057" 1 1 1079 "32343802800033"
"a05114000281" 1 0 1108 "37767984000100"
"a05314000157" 1 0 1145 "78624942500012"
"a05316001223" 6 1  381 "55655032500030"
"a05412000544" 1 1 1655 "76080006000013"
"a05414000287" 6 1 1139 "34279309800027"
"a05414000302" 6 1 1101 "41119888000022"
"a05417002944" 1 0  184 "78692030600226"
"a05417002950" 1 0  219 "39208391100090"
"a05417002973" 1 0   44 "76780012100018"
"a05715001285" 1 0  982 "78692030600176"
"a05715001734" 1 1  655 "40311943100027"
"a05716002334" 1 1  583 "78004244600010"
"a06014000476" 1 0 1031 "51774658200033"
"a06014000975" 1 1  761 "42821644400029"
"a06015001099" 1 1  776 "38016095200020"
"a06016002680" 1 0  250 "54206915800591"
"a06212000123" 1 1 1800 "35720005400017"
"a06212000265" 1 1 1835 "36120038900019"
"a06212000387" 1 1 1744 "43402994800016"
"a06212000638" 1 1 1649 "37020053700014"
"a06212000788" 1 1 1636 "40799716200042"
"a06212001065" 1 0 1529 "44133063600024"
"a06215003640" 1 1  397 "35720005400017"
"a06216003745" 6 1  355 "78409871700035"
"a06216004369" 1 1  208 "41031487600187"
end
label values type type
label def type 1 "good", modify
label def type 6 "still good", modify
    Last edited by Ylenia Curci; 25 Sep 2021, 07:22.
    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str12 documentid float(type amel datetake) str14 firmid
"a07512000384" 1 1 1838 "33919668500012"
"a00113000548" 1 1 1282 "45060988800018"
"a00115000892" 1 1  731 "32343851500021"
"a00115001035" 1 0  668 "77554456200108"
"a00116002086" 6 1  221 "55208131715412"
"a00212000444" 1 1 1648 "58558085500104"
"a00216001069" 1 0  476 "39451475600029"
"a00312000086" 1 1 1851 "41873471100017"
"a00313000308" 1 0 1209 "38289442600016"
"a00414000040" 1 0 1044 "30582329600077"
"a00716000762" 6 0  206 "55208131715461"
"a00815000275" 1 1  680 "77734395500061"
"a01014000094" 1 0 1004 "56288017900030"
"a01015000317" 1 1  646 "56288017900030"
"a01314000477" 1 0 1130 "30233237400016"
"a01314001232" 1 1 1018 "07280174900010"
"a01414000197" 6 1 1058 "50453874500028"
"a01415000895" 1 0  681 "30634863200093"
"a01415001076" 1 0  915 "39821962600015"
"a01415001102" 1 1  654 "54201695101419"
"a01416001972" 1 0  257 "64382064000016"
"a01416002249" 1 0  382 "39821962600015"
"a01416002493" 1 0  156 "57206768400660"
"a01616001032" 6 1  298 "90572051200011"
"a01616001086" 6 1  268 "60182032700034"
"a01616001312" 1 1  206 "41317505000058"
"a01715001072" 6 1  649 "31851878400018"
"a01814000022" 6 0 1136 "32246383700011"
"a01814000107" 1 0 1019 "51772011600022"
"a01815000539" 6 1  388 "32246383700011"
"a01816000777" 6 1   17 "32246383700011"
"a01817000807" 6 1   19 "38211923800030"
"a02213000201" 1 1 1453 "77745617900020"
"a02516001917" 1 1  180 "44471927200148"
"a02614000281" 1 1 1023 "77945636700019"
"a02813000099" 1 1 1443 "31439767000048"
"a02813000447" 6 1 1207 "34888467700132"
"a02814000161" 1 0 1047 "31439767000048"
"a02814000458" 1 1  779 "77567898000092"
"a02914001078" 1 1 1072 "34757035000038"
"a02915001759" 1 1  599 "77622146700017"
"a02915002054" 1 1  599 "54695037900034"
"a02916003431" 6 1   65 "80261109500026"
"a02917003541" 1 1   67 "39931174500026"
"a03113000810" 1 1 1283 "55201683400053"
"a03312000044" 1 1 1843 "89685028600015"
"a03312000056" 1 0 1842 "52558019700057"
"a03312000127" 1 1 1836 "48770604600025"
"a03312000266" 1 1 1835 "43465124600010"
"a03312000837" 1 1 1719 "47020299500028"
"a03312001050" 1 1 1649 "31922755900014"
"a03312001404" 1 1 1626 "43331347500025"
"a03313000321" 1 1 1496 "78183171400014"
"a03313000863" 1 1 1438 "45620494000542"
"a03313001750" 1 0 1153 "45520353900016"
"a03314000360" 1 0 1041 "77811582401699"
"a03314000478" 1 0 1023 "42405982200256"
"a03314000824" 1 0  991 "30352750100033"
"a03315001901" 1 0  750 "45520353900016"
"a03315001977" 1 1  744 "46320174900033"
"a03315002487" 1 0  641 "30352750100033"
"a03316003901" 1 0  327 "77811582401699"
"a03316004007" 1 0  302 "42405982200256"
"a03316004039" 1 0  316 "52558019700057"
"a03316004098" 1 0  298 "48770604600025"
"a03316004315" 1 0  261 "43331347500025"
"a03513001217" 1 0 1249 "32029220400015"
"a03517005779" 6 1   17 "39939393300052"
"a03716002686" 6 1  225 "55208131724224"
"a04416004299" 6 1  379 "33890331300025"
"a04416005141" 6 0  253 "55208131736210"
"a04517002780" 1 1   22 "08628039300041"
"a04715000316" 1 1  793 "38067825000016"
"a04716000720" 6 0  376 "72705008000017"
"a04914000057" 1 1 1079 "32343802800033"
"a05114000281" 1 0 1108 "37767984000100"
"a05314000157" 1 0 1145 "78624942500012"
"a05316001223" 6 1  381 "55655032500030"
"a05412000544" 1 1 1655 "76080006000013"
"a05414000287" 6 1 1139 "34279309800027"
"a05414000302" 6 1 1101 "41119888000022"
"a05417002944" 1 0  184 "78692030600226"
"a05417002950" 1 0  219 "39208391100090"
"a05417002973" 1 0   44 "76780012100018"
"a05715001285" 1 0  982 "78692030600176"
"a05715001734" 1 1  655 "40311943100027"
"a05716002334" 1 1  583 "78004244600010"
"a06014000476" 1 0 1031 "51774658200033"
"a06014000975" 1 1  761 "42821644400029"
"a06015001099" 1 1  776 "38016095200020"
"a06016002680" 1 0  250 "54206915800591"
"a06212000123" 1 1 1800 "35720005400017"
"a06212000265" 1 1 1835 "36120038900019"
"a06212000387" 1 1 1744 "43402994800016"
"a06212000638" 1 1 1649 "37020053700014"
"a06212000788" 1 1 1636 "40799716200042"
"a06212001065" 1 0 1529 "44133063600024"
"a06215003640" 1 1  397 "35720005400017"
"a06216003745" 6 1  355 "78409871700035"
"a06216004369" 1 1  208 "41031487600187"
end
label values type type
label def type 1 "good", modify
label def type 6 "still good", modify

bys firmid: egen has1_6= max(cond(type==1|type==6, 1, 0)) 
bys firmid (datetake): gen selected= cond(sum(sum(has1_6& (type==1|type==6)))==1, 1, cond(amel & !has1_6, 1, 0))
    Res.:

    Code:
    . sort firmid documentid

. l, sepby(firmid)

     +----------------------------------------------------------------------------------+
     |   documentid         type   amel   datetake           firmid   has1_6   selected |
     |----------------------------------------------------------------------------------|
  1. | a01314001232         good      1       1018   07280174900010        1          1 |
     |----------------------------------------------------------------------------------|
  2. | a04517002780         good      1         22   08628039300041        1          1 |
     |----------------------------------------------------------------------------------|
  3. | a01314000477         good      0       1130   30233237400016        1          1 |
     |----------------------------------------------------------------------------------|
  4. | a03314000824         good      0        991   30352750100033        1          0 |
  5. | a03315002487         good      0        641   30352750100033        1          1 |
     |----------------------------------------------------------------------------------|
  6. | a00414000040         good      0       1044   30582329600077        1          1 |
     |----------------------------------------------------------------------------------|
  7. | a01415000895         good      0        681   30634863200093        1          1 |
     |----------------------------------------------------------------------------------|
  8. | a02813000099         good      1       1443   31439767000048        1          0 |
  9. | a02814000161         good      0       1047   31439767000048        1          1 |
     |----------------------------------------------------------------------------------|
 10. | a01715001072   still good      1        649   31851878400018        1          1 |
     |----------------------------------------------------------------------------------|
 11. | a03312001050         good      1       1649   31922755900014        1          1 |
     |----------------------------------------------------------------------------------|
 12. | a03513001217         good      0       1249   32029220400015        1          1 |
     |----------------------------------------------------------------------------------|
 13. | a01814000022   still good      0       1136   32246383700011        1          0 |
 14. | a01815000539   still good      1        388   32246383700011        1          0 |
 15. | a01816000777   still good      1         17   32246383700011        1          1 |
     |----------------------------------------------------------------------------------|
 16. | a04914000057         good      1       1079   32343802800033        1          1 |
     |----------------------------------------------------------------------------------|
 17. | a00115000892         good      1        731   32343851500021        1          1 |
     |----------------------------------------------------------------------------------|
 18. | a04416004299   still good      1        379   33890331300025        1          1 |
     |----------------------------------------------------------------------------------|
 19. | a07512000384         good      1       1838   33919668500012        1          1 |
     |----------------------------------------------------------------------------------|
 20. | a05414000287   still good      1       1139   34279309800027        1          1 |
     |----------------------------------------------------------------------------------|
 21. | a02914001078         good      1       1072   34757035000038        1          1 |
     |----------------------------------------------------------------------------------|
 22. | a02813000447   still good      1       1207   34888467700132        1          1 |
     |----------------------------------------------------------------------------------|
 23. | a06212000123         good      1       1800   35720005400017        1          0 |
 24. | a06215003640         good      1        397   35720005400017        1          1 |
     |----------------------------------------------------------------------------------|
 25. | a06212000265         good      1       1835   36120038900019        1          1 |
     |----------------------------------------------------------------------------------|
 26. | a06212000638         good      1       1649   37020053700014        1          1 |
     |----------------------------------------------------------------------------------|
 27. | a05114000281         good      0       1108   37767984000100        1          1 |
     |----------------------------------------------------------------------------------|
 28. | a06015001099         good      1        776   38016095200020        1          1 |
     |----------------------------------------------------------------------------------|
 29. | a04715000316         good      1        793   38067825000016        1          1 |
     |----------------------------------------------------------------------------------|
 30. | a01817000807   still good      1         19   38211923800030        1          1 |
     |----------------------------------------------------------------------------------|
 31. | a00313000308         good      0       1209   38289442600016        1          1 |
     |----------------------------------------------------------------------------------|
 32. | a05417002950         good      0        219   39208391100090        1          1 |
     |----------------------------------------------------------------------------------|
 33. | a00216001069         good      0        476   39451475600029        1          1 |
     |----------------------------------------------------------------------------------|
 34. | a01415001076         good      0        915   39821962600015        1          0 |
 35. | a01416002249         good      0        382   39821962600015        1          1 |
     |----------------------------------------------------------------------------------|
 36. | a02917003541         good      1         67   39931174500026        1          1 |
     |----------------------------------------------------------------------------------|
 37. | a03517005779   still good      1         17   39939393300052        1          1 |
     |----------------------------------------------------------------------------------|
 38. | a05715001734         good      1        655   40311943100027        1          1 |
     |----------------------------------------------------------------------------------|
 39. | a06212000788         good      1       1636   40799716200042        1          1 |
     |----------------------------------------------------------------------------------|
 40. | a06216004369         good      1        208   41031487600187        1          1 |
     |----------------------------------------------------------------------------------|
 41. | a05414000302   still good      1       1101   41119888000022        1          1 |
     |----------------------------------------------------------------------------------|
 42. | a01616001312         good      1        206   41317505000058        1          1 |
     |----------------------------------------------------------------------------------|
 43. | a00312000086         good      1       1851   41873471100017        1          1 |
     |----------------------------------------------------------------------------------|
 44. | a03314000478         good      0       1023   42405982200256        1          0 |
 45. | a03316004007         good      0        302   42405982200256        1          1 |
     |----------------------------------------------------------------------------------|
 46. | a06014000975         good      1        761   42821644400029        1          1 |
     |----------------------------------------------------------------------------------|
 47. | a03312001404         good      1       1626   43331347500025        1          0 |
 48. | a03316004315         good      0        261   43331347500025        1          1 |
     |----------------------------------------------------------------------------------|
 49. | a06212000387         good      1       1744   43402994800016        1          1 |
     |----------------------------------------------------------------------------------|
 50. | a03312000266         good      1       1835   43465124600010        1          1 |
     |----------------------------------------------------------------------------------|
 51. | a06212001065         good      0       1529   44133063600024        1          1 |
     |----------------------------------------------------------------------------------|
 52. | a02516001917         good      1        180   44471927200148        1          1 |
     |----------------------------------------------------------------------------------|
 53. | a00113000548         good      1       1282   45060988800018        1          1 |
     |----------------------------------------------------------------------------------|
 54. | a03313001750         good      0       1153   45520353900016        1          0 |
 55. | a03315001901         good      0        750   45520353900016        1          1 |
     |----------------------------------------------------------------------------------|
 56. | a03313000863         good      1       1438   45620494000542        1          1 |
     |----------------------------------------------------------------------------------|
 57. | a03315001977         good      1        744   46320174900033        1          1 |
     |----------------------------------------------------------------------------------|
 58. | a03312000837         good      1       1719   47020299500028        1          1 |
     |----------------------------------------------------------------------------------|
 59. | a03312000127         good      1       1836   48770604600025        1          0 |
 60. | a03316004098         good      0        298   48770604600025        1          1 |
     |----------------------------------------------------------------------------------|
 61. | a01414000197   still good      1       1058   50453874500028        1          1 |
     |----------------------------------------------------------------------------------|
 62. | a01814000107         good      0       1019   51772011600022        1          1 |
     |----------------------------------------------------------------------------------|
 63. | a06014000476         good      0       1031   51774658200033        1          1 |
     |----------------------------------------------------------------------------------|
 64. | a03312000056         good      0       1842   52558019700057        1          0 |
 65. | a03316004039         good      0        316   52558019700057        1          1 |
     |----------------------------------------------------------------------------------|
 66. | a01415001102         good      1        654   54201695101419        1          1 |
     |----------------------------------------------------------------------------------|
 67. | a06016002680         good      0        250   54206915800591        1          1 |
     |----------------------------------------------------------------------------------|
 68. | a02915002054         good      1        599   54695037900034        1          1 |
     |----------------------------------------------------------------------------------|
 69. | a03113000810         good      1       1283   55201683400053        1          1 |
     |----------------------------------------------------------------------------------|
 70. | a00116002086   still good      1        221   55208131715412        1          1 |
     |----------------------------------------------------------------------------------|
 71. | a00716000762   still good      0        206   55208131715461        1          1 |
     |----------------------------------------------------------------------------------|
 72. | a03716002686   still good      1        225   55208131724224        1          1 |
     |----------------------------------------------------------------------------------|
 73. | a04416005141   still good      0        253   55208131736210        1          1 |
     |----------------------------------------------------------------------------------|
 74. | a05316001223   still good      1        381   55655032500030        1          1 |
     |----------------------------------------------------------------------------------|
 75. | a01014000094         good      0       1004   56288017900030        1          0 |
 76. | a01015000317         good      1        646   56288017900030        1          1 |
     |----------------------------------------------------------------------------------|
 77. | a01416002493         good      0        156   57206768400660        1          1 |
     |----------------------------------------------------------------------------------|
 78. | a00212000444         good      1       1648   58558085500104        1          1 |
     |----------------------------------------------------------------------------------|
 79. | a01616001086   still good      1        268   60182032700034        1          1 |
     |----------------------------------------------------------------------------------|
 80. | a01416001972         good      0        257   64382064000016        1          1 |
     |----------------------------------------------------------------------------------|
 81. | a04716000720   still good      0        376   72705008000017        1          1 |
     |----------------------------------------------------------------------------------|
 82. | a05412000544         good      1       1655   76080006000013        1          1 |
     |----------------------------------------------------------------------------------|
 83. | a05417002973         good      0         44   76780012100018        1          1 |
     |----------------------------------------------------------------------------------|
 84. | a00115001035         good      0        668   77554456200108        1          1 |
     |----------------------------------------------------------------------------------|
 85. | a02814000458         good      1        779   77567898000092        1          1 |
     |----------------------------------------------------------------------------------|
 86. | a02915001759         good      1        599   77622146700017        1          1 |
     |----------------------------------------------------------------------------------|
 87. | a00815000275         good      1        680   77734395500061        1          1 |
     |----------------------------------------------------------------------------------|
 88. | a02213000201         good      1       1453   77745617900020        1          1 |
     |----------------------------------------------------------------------------------|
 89. | a03314000360         good      0       1041   77811582401699        1          0 |
 90. | a03316003901         good      0        327   77811582401699        1          1 |
     |----------------------------------------------------------------------------------|
 91. | a02614000281         good      1       1023   77945636700019        1          1 |
     |----------------------------------------------------------------------------------|
 92. | a05716002334         good      1        583   78004244600010        1          1 |
     |----------------------------------------------------------------------------------|
 93. | a03313000321         good      1       1496   78183171400014        1          1 |
     |----------------------------------------------------------------------------------|
 94. | a06216003745   still good      1        355   78409871700035        1          1 |
     |----------------------------------------------------------------------------------|
 95. | a05314000157         good      0       1145   78624942500012        1          1 |
     |----------------------------------------------------------------------------------|
 96. | a05715001285         good      0        982   78692030600176        1          1 |
     |----------------------------------------------------------------------------------|
 97. | a05417002944         good      0        184   78692030600226        1          1 |
     |----------------------------------------------------------------------------------|
 98. | a02916003431   still good      1         65   80261109500026        1          1 |
     |----------------------------------------------------------------------------------|
 99. | a03312000044         good      1       1843   89685028600015        1          1 |
     |----------------------------------------------------------------------------------|
100. | a01616001032   still good      1        298   90572051200011        1          1 |
     +----------------------------------------------------------------------------------+

    Comment

    • #3
      Thank you, this works perfectly!

      Y

      Comment

      Previous Next
      Working...
      X