Remove string text using the contents of another variable

Jared Greathouse

Join Date: Sep 2021
Posts: 2170

Remove string text using the contents of another variable

23 Mar 2022, 12:21

Hey everyone. I'm creating a panel dataset, and I want to create separate IDs for each state and municipio in question. To get the municipal ID, it seems like the way to go is to delete the contents of the ADM2_PCODE variable by using the string contents of the ADM1_PCODE. For example, I wanna take the BR1301704 string from ADM2_PCODE and make it into 01704, using the string supplied from ADM1_PCODE. Presumably, a string function is the key here...

Here's my data.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input int _ID double(_CX _CY) str6(ADM0_EN ADM0_PT) str2 ADM0_PCODE str19 ADM1_PT str4 ADM1_PCODE str33 ADM2_PT str9 ADM2_PCODE int ET_ID
  1  -62.27465502077262 -12.470169428269113 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Alta Floresta D'Oeste"      "BR1100015"  0
  2  -62.95711437056544  -9.951921228943078 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Ariquemes"                  "BR1100023"  1
  3  -60.63967066249615 -13.474751749674386 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Cabixi"                     "BR1100031"  2
  4  -61.32462450889516   -11.3013012565372 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Cacoal"                     "BR1100049"  3
  5 -61.260931024900025 -13.203518886680056 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Cerejeiras"                 "BR1100056"  4
  6  -60.55026739593692 -13.159059605120861 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Colorado do Oeste"          "BR1100064"  5
  7 -61.091566270621136  -12.92660119552339 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Corumbiara"                 "BR1100072"  6
  8  -64.05876740785295 -12.147207895339157 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Costa Marques"              "BR1100080"  7
  9  -60.78477950994642 -11.351309973913336 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Espigão D'Oeste"           "BR1100098"  8
 10  -64.53743524627615 -11.305621838598759 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Guajará-Mirim"             "BR1100106"  9
 11  -62.58474839289158 -10.592408705893646 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Jaru"                       "BR1100114" 10
 12 -61.757207308031006 -10.462197694963178 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Ji-Paraná"                 "BR1100122" 11
 13  -62.01031905644597  -9.204058151414646 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Machadinho D'Oeste"         "BR1100130" 12
 14  -62.28019534273658 -11.643047646996877 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Nova Brasilândia D'Oeste"  "BR1100148" 13
 15 -62.172543145703514 -10.610679353506022 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Ouro Preto do Oeste"        "BR1100155" 14
 16 -60.830497172711404 -11.894413203091439 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Pimenta Bueno"              "BR1100189" 15
 17  -64.30669358722673    -9.1535225468222 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Porto Velho"                "BR1100205" 16
 18  -61.87787645640593  -11.16632053361317 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Presidente Médici"         "BR1100254" 17
 19 -62.790058152716256   -9.64514755819473 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Rio Crespo"                 "BR1100262" 18
 20  -61.77149637738723 -11.732556354032807 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Rolim de Moura"             "BR1100288" 19
 21  -61.70105000288009 -12.027115920663107 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Santa Luzia D'Oeste"        "BR1100296" 20
 22  -60.24844167584098 -12.095628437573058 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Vilhena"                    "BR1100304" 21
 23  -62.98314991475225 -11.614879783080807 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "São Miguel do Guaporé"    "BR1100320" 22
 24  -64.62921558107023 -10.382120423337861 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Nova Mamoré"               "BR1100338" 23
 25  -62.54488445485896 -11.311389906730161 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Alvorada D'Oeste"           "BR1100346" 24
 26  -61.88077156964357 -12.593654334641656 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Alto Alegre dos Parecis"    "BR1100379" 25
 27  -63.39423562706692  -9.649185619461123 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Alto Paraíso"              "BR1100403" 26
 28  -63.94114596997432 -10.052053095897678 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Buritis"                    "BR1100452" 27
 29  -62.08316922542399 -11.681048785179659 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Novo Horizonte do Oeste"    "BR1100502" 28
 30   -62.9870449597786  -10.33167202307913 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Cacaulândia"               "BR1100601" 29
 31  -63.79962573252475 -10.484907988514484 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Campo Novo de Rondônia"    "BR1100700" 30
 32   -63.3256223652308   -8.88703798888496 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Candeias do Jamari"         "BR1100809" 31
 33  -61.88004076531139  -11.47079131367724 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Castanheiras"               "BR1100908" 32
 34  -60.89851863312522 -12.537371241749936 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Chupinguaia"                "BR1100924" 33
 35 -62.565296476755016  -9.171032214767523 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Cujubim"                    "BR1100940" 34
 36 -63.183974345845435 -10.747947137909136 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Governador Jorge Teixeira"  "BR1101005" 35
 37 -63.044876038378796  -9.170182342853343 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Itapuã do Oeste"           "BR1101104" 36
 38 -61.568883112194044  -11.16025018303252 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Ministro Andreazza"         "BR1101203" 37
 39  -62.84790153945934 -11.076807289839977 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Mirante da Serra"           "BR1101302" 38
 40  -63.37245336550688  -10.21063718662686 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Monte Negro"                "BR1101401" 39
 41 -62.532321278706576 -10.889323662700612 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Nova União"                "BR1101435" 40
 42  -61.41524957771943 -12.262218278721976 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Parecis"                    "BR1101450" 41
 43  -61.54530629995434  -13.17382452776814 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Pimenteiras do Oeste"       "BR1101468" 42
 44  -61.31530624540729  -11.87885534091451 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Primavera de Rondônia"     "BR1101476" 43
 45  -61.46708966140997 -11.904769107569049 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "São Felipe D'Oeste"        "BR1101484" 44
 46  -63.12204225572851 -12.368700882693712 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "São Francisco do Guaporé" "BR1101492" 45
 47  -63.23070462235022 -11.807190106163574 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Seringueiras"               "BR1101500" 46
 48   -62.2797036796397  -10.94357798194909 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Teixeirópolis"             "BR1101559" 47
 49  -62.34250655708124 -10.121743244974788 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Theobroma"                  "BR1101609" 48
 50  -62.42467001515514 -11.103949114163465 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Urupá"                     "BR1101708" 49
 51  -61.93909533709719   -9.73245428876751 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Vale do Anari"              "BR1101757" 50
 52 -62.094023024179236 -10.384252069704807 "Brazil" "Brasil" "BR" "Rondônia" "BR11" "Vale do Paraíso"           "BR1101807" 51
 53  -66.95038397110609  -9.933620074632309 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Acrelândia"                "BR1200013" 52
 54  -70.00585145285847 -10.779061889927872 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Assis Brasil"               "BR1200054" 53
 55  -69.20762869506416 -10.746632015043593 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Brasiléia"                 "BR1200104" 54
 56  -68.17370951014776  -9.614230798863447 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Bujari"                     "BR1200138" 55
 57  -67.83601277428764 -10.473628354574643 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Capixaba"                   "BR1200179" 56
 58  -72.75478239027679  -7.971907624024788 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Cruzeiro do Sul"            "BR1200203" 57
 59  -68.61804264677713 -10.888662432941631 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Epitaciolândia"            "BR1200252" 58
 60  -70.74473025579672  -8.924420641444478 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Feijó"                     "BR1200302" 59
 61    -71.905996915258  -9.242504037194113 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Jordão"                    "BR1200328" 60
 62  -73.43894770150047  -7.504409799068428 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Mâncio Lima"               "BR1200336" 61
 63  -69.85391519494839  -9.381228687855419 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Manoel Urbano"              "BR1200344" 62
 64  -72.55929439552183  -9.095931438040546 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Marechal Thaumaturgo"       "BR1200351" 63
 65  -67.33991513359575 -10.248887065918085 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Plácido de Castro"         "BR1200385" 64
 66   -72.7334357697015  -8.491631123303497 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Porto Walter"               "BR1200393" 65
 67  -68.37117113272882  -10.06616499766153 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Rio Branco"                 "BR1200401" 66
 68  -73.22329898252265  -7.843754184346975 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Rodrigues Alves"            "BR1200427" 67
 69  -70.39546882650852  -9.410182631688409 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Santa Rosa do Purus"        "BR1200435" 68
 70   -67.4271593637568  -9.997135469869667 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Senador Guiomard"           "BR1200450" 69
 71  -69.38163144798412  -9.771218354770241 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Sena Madureira"             "BR1200500" 70
 72  -71.44489736917946  -8.239375117771052 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Tarauacá"                  "BR1200609" 71
 73  -68.50446401505138 -10.575514447388777 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Xapuri"                     "BR1200708" 72
 74   -67.6983304666378  -9.629533904648612 "Brazil" "Brasil" "BR" "Acre"      "BR12" "Porto Acre"                 "BR1200807" 73
 75  -65.32440516582064  -3.694639946588858 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Alvarães"                  "BR1300029" 74
 76  -68.28650212574779 -3.4184054930328083 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Amaturá"                   "BR1300060" 75
 77  -61.71792903450668 -3.5018242718260324 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Anamã"                     "BR1300086" 76
 78  -62.09537582656932 -4.1986479580798655 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Anori"                      "BR1300102" 77
 79   -59.4320628929787  -7.759930962273845 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Apuí"                      "BR1300144" 78
 80  -71.74134737293285  -5.711863135310497 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Atalaia do Norte"           "BR1300201" 79
 81  -59.52548786779156  -3.787165077332518 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Autazes"                    "BR1300300" 80
 82  -63.43468086874544 -.46506210178854535 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Barcelos"                   "BR1300409" 81
 83  -57.17232415219478  -3.129009013591088 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Barreirinha"                "BR1300508" 82
 84  -70.27611436232846  -5.426724637319639 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Benjamin Constant"          "BR1300607" 83
 85  -61.80427689220407  -4.606160690029038 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Beruri"                     "BR1300631" 84
 86  -57.67003564698124 -3.1377250367397305 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Boa Vista do Ramos"         "BR1300680" 85
 87  -68.04765388894359  -8.773197169959646 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Boca do Acre"               "BR1300706" 86
 88 -59.534662476767515  -4.957572906742065 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Borba"                      "BR1300805" 87
 89  -61.87772499984877  -3.081987511306432 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Caapiranga"                 "BR1300839" 88
 90  -64.06187887978547  -7.300732571562458 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Canutama"                   "BR1300904" 89
 91  -67.33746819555601  -5.118523164585786 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Carauari"                   "BR1301001" 90
 92  -60.18322978098035 -3.7704472489443255 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Careiro"                    "BR1301100" 91
 93  -59.59862961042921 -3.2859634996367135 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Careiro da Várzea"         "BR1301159" 92
 94  -64.12527471618647  -4.436312328536398 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Coari"                      "BR1301209" 93
 95  -62.97328609044677 -3.2889347122060952 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Codajás"                   "BR1301308" 94
 96  -70.21818152173257  -7.021953922608278 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Eirunepé"                  "BR1301407" 95
 97  -70.05505282781498  -7.573339212220005 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Envira"                     "BR1301506" 96
 98  -66.35786989267181 -2.4909169216120044 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Fonte Boa"                  "BR1301605" 97
 99  -72.84458970521392  -7.229752110194079 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Guajará"                   "BR1301654" 98
100 -62.486383028822665  -7.361195773288935 "Brazil" "Brasil" "BR" "Amazonas"  "BR13" "Humaitá"                   "BR1301704" 99
end

How might I do this?

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 9957

23 Mar 2022, 12:49

If you want the leading zeros, then remove the -real()- function from the code. Otherwise, see

Code:

help subinstr()
help regexr()
help ustrregexra()

Code:

gen wanted1= real(subinstr(ADM2_PCODE, ADM1_PCODE, "", 1))
gen wanted2= real(ustrregexra(ADM2_PCODE, ADM1_PCODE, "", 1))

Res.:

Code:

. l ADM1_PCODE ADM2_PCODE wanted1 wanted2, sep(0)

     +------------------------------------------+
     | ADM1_P~E   ADM2_PC~E   wanted1   wanted2 |
     |------------------------------------------|
  1. |     BR11   BR1100015        15        15 |
  2. |     BR11   BR1100023        23        23 |
  3. |     BR11   BR1100031        31        31 |
  4. |     BR11   BR1100049        49        49 |
  5. |     BR11   BR1100056        56        56 |
  6. |     BR11   BR1100064        64        64 |
  7. |     BR11   BR1100072        72        72 |
  8. |     BR11   BR1100080        80        80 |
  9. |     BR11   BR1100098        98        98 |
 10. |     BR11   BR1100106       106       106 |
 11. |     BR11   BR1100114       114       114 |
 12. |     BR11   BR1100122       122       122 |
 13. |     BR11   BR1100130       130       130 |
 14. |     BR11   BR1100148       148       148 |
 15. |     BR11   BR1100155       155       155 |
 16. |     BR11   BR1100189       189       189 |
 17. |     BR11   BR1100205       205       205 |
 18. |     BR11   BR1100254       254       254 |
 19. |     BR11   BR1100262       262       262 |
 20. |     BR11   BR1100288       288       288 |
 21. |     BR11   BR1100296       296       296 |
 22. |     BR11   BR1100304       304       304 |
 23. |     BR11   BR1100320       320       320 |
 24. |     BR11   BR1100338       338       338 |
 25. |     BR11   BR1100346       346       346 |
 26. |     BR11   BR1100379       379       379 |
 27. |     BR11   BR1100403       403       403 |
 28. |     BR11   BR1100452       452       452 |
 29. |     BR11   BR1100502       502       502 |
 30. |     BR11   BR1100601       601       601 |
 31. |     BR11   BR1100700       700       700 |
 32. |     BR11   BR1100809       809       809 |
 33. |     BR11   BR1100908       908       908 |
 34. |     BR11   BR1100924       924       924 |
 35. |     BR11   BR1100940       940       940 |
 36. |     BR11   BR1101005      1005      1005 |
 37. |     BR11   BR1101104      1104      1104 |
 38. |     BR11   BR1101203      1203      1203 |
 39. |     BR11   BR1101302      1302      1302 |
 40. |     BR11   BR1101401      1401      1401 |
 41. |     BR11   BR1101435      1435      1435 |
 42. |     BR11   BR1101450      1450      1450 |
 43. |     BR11   BR1101468      1468      1468 |
 44. |     BR11   BR1101476      1476      1476 |
 45. |     BR11   BR1101484      1484      1484 |
 46. |     BR11   BR1101492      1492      1492 |
 47. |     BR11   BR1101500      1500      1500 |
 48. |     BR11   BR1101559      1559      1559 |
 49. |     BR11   BR1101609      1609      1609 |
 50. |     BR11   BR1101708      1708      1708 |
 51. |     BR11   BR1101757      1757      1757 |
 52. |     BR11   BR1101807      1807      1807 |
 53. |     BR12   BR1200013        13        13 |
 54. |     BR12   BR1200054        54        54 |
 55. |     BR12   BR1200104       104       104 |
 56. |     BR12   BR1200138       138       138 |
 57. |     BR12   BR1200179       179       179 |
 58. |     BR12   BR1200203       203       203 |
 59. |     BR12   BR1200252       252       252 |
 60. |     BR12   BR1200302       302       302 |
 61. |     BR12   BR1200328       328       328 |
 62. |     BR12   BR1200336       336       336 |
 63. |     BR12   BR1200344       344       344 |
 64. |     BR12   BR1200351       351       351 |
 65. |     BR12   BR1200385       385       385 |
 66. |     BR12   BR1200393       393       393 |
 67. |     BR12   BR1200401       401       401 |
 68. |     BR12   BR1200427       427       427 |
 69. |     BR12   BR1200435       435       435 |
 70. |     BR12   BR1200450       450       450 |
 71. |     BR12   BR1200500       500       500 |
 72. |     BR12   BR1200609       609       609 |
 73. |     BR12   BR1200708       708       708 |
 74. |     BR12   BR1200807       807       807 |
 75. |     BR13   BR1300029        29        29 |
 76. |     BR13   BR1300060        60        60 |
 77. |     BR13   BR1300086        86        86 |
 78. |     BR13   BR1300102       102       102 |
 79. |     BR13   BR1300144       144       144 |
 80. |     BR13   BR1300201       201       201 |
 81. |     BR13   BR1300300       300       300 |
 82. |     BR13   BR1300409       409       409 |
 83. |     BR13   BR1300508       508       508 |
 84. |     BR13   BR1300607       607       607 |
 85. |     BR13   BR1300631       631       631 |
 86. |     BR13   BR1300680       680       680 |
 87. |     BR13   BR1300706       706       706 |
 88. |     BR13   BR1300805       805       805 |
 89. |     BR13   BR1300839       839       839 |
 90. |     BR13   BR1300904       904       904 |
 91. |     BR13   BR1301001      1001      1001 |
 92. |     BR13   BR1301100      1100      1100 |
 93. |     BR13   BR1301159      1159      1159 |
 94. |     BR13   BR1301209      1209      1209 |
 95. |     BR13   BR1301308      1308      1308 |
 96. |     BR13   BR1301407      1407      1407 |
 97. |     BR13   BR1301506      1506      1506 |
 98. |     BR13   BR1301605      1605      1605 |
 99. |     BR13   BR1301654      1654      1654 |
100. |     BR13   BR1301704      1704      1704 |
     +------------------------------------------+

.

Comment

Øyvind Snilsberg

Join Date: Oct 2021

Posts: 591
#3

23 Mar 2022, 13:00

gen wanted = subinstr(ADM2_PCODE,word(ADM1_PCODE,1),"",.)
1 like
Comment
Jared Greathouse

Join Date: Sep 2021

Posts: 2170
#4

23 Mar 2022, 13:45

Yep this worked, thank you both so much!
Comment

Announcement

Remove string text using the contents of another variable

Comment

Comment

Comment