Creating two new address variables based on an existing variable

[Luis Mijares Castaneda]

Hello, I have the variable PERSONALADDRESS which a sample of is shown below, I want to create two new variables based on this address variable, one variable must contain the street address and another variable must contain the city, state. zipcode, so for example I the address "29 BROKEN ARROW RD BRACKETVILLE, TX 78832 UNITED STATES" must be broken into two distinct variables, one must have 29 BROKEN ARROW RD the other address much be BRACKETVILE, TX 78832 United States. The issue I was having is that the addresses are composed of different lengths and different styles. Any help would be much appreciated.

* Example generated by -dataex-. For more info, type help dataex
clear
input str89 PERSONALADDRESS
"29 BROKEN ARROW RD BRACKETVILLE, TX 78832 UNITED STATES"        
"189 EMPRESARIO DR UBC, TX 78253 UNITED STATES"                  
"305 E LEROY ST THREE RIVERS, TEXAS 78071 UNITED STATES"         
""                                                               
"276 SADDLE HORN DR BANDERA, TX 78003 UNITED STATES"             
"756 PURPLE SAGE DR BANDERA, TX 78003 UNITED STATES"             
"151 SHADOW VALLEY SAN ANTONIO, TX 78227 UNITED STATES"          
"8134 GRISSOM SAN ANTONIO, TX 78251"                             
""                                                               
""                                                               
"1624 W SALINAS SAN ANTONIO, TX 78207 UNITED STATES"             
"11035 INNER CYN SAN ANTONIO, TX 78252"                          
"11035 INNER CYN SAN ANTONIO, TX 78252"                          
"11035 INNER CYN SAN ANTONIO, TX 78252"                          
"7938 HANGING BRANCH UBC, TX 78253 UNITED STATES"                
"1203 TENBURY SAN ANTONIO, TX 78253"                             
""                                                               
"213 DEL VALLE SAN ANTONIO, TX 78207"                            
"213 DEL VALLE SAN ANTONIO, TX 78207"                            
"710 NOVELLA AVE ADKINS, TX 78101"                               
"13003 ESSEN FOREST SAN ANTONIO, TX 78023 UNITED STATES"         
"7922 PARKLAND GREEN DR SAN ANTONIO, TX 78240 UNITED STATES"     
"5322 EBONY SAN ANTONIO, TX 78228"                               
""                                                               
"3106 CLIMBING ROSE SAN ANTONIO, TX 78230 UNITED STATES"         
"511 W BAYLOR SAN ANTONIO, TX 78204 UNITED STATES"               
"511 W BAYLOR SAN ANTONIO, TX 78204 UNITED STATES"               
"511 W BAYLOR SAN ANTONIO, TX 78204 UNITED STATES"               
"5110 BEVERLY DR SAN ANGELO, TEXAS 76904 UNITED STATES"          
"2915 HIGHCLIFF SAN ANTONIO, TX 78218"                           
"18595 HOLLYBRANCH CT PORTER, TEXAS 77365 UNITED STATES"         
"18595 HOLLYBRANCH CT PORTER, TEXAS 77365 UNITED STATES"         
"12606 MIDDLE LN SAN ANTONIO, TX 78217 UNITED STATES"            
"2193 CR 342 LA VERNIA, TX 78121 UNITED STATES"                  
"2815 BRIAFIELD SAN ANTONIO, TX"                                 
"2815 BRIAFIELD SAN ANTONIO, TX"                                 
"2815 BRIAFIELD SAN ANTONIO, TX"                                 
"2815 BRIAFIELD SAN ANTONIO, TX"                                 
"1630 CRYSTAL BRIDGES UBC, TX 78260 UNITED STATES"               
"1630 CRYSTAL BRIDGES UBC, TX 78260 UNITED STATES"               
"1630 CRYSTAL BRIDGES UBC, TX 78260 UNITED STATES"               
"8726 DISCOVERY WAY CONVERSE, TX 78109"                          
""                                                               
""                                                               
"3843 KILLARNEY SAN ANTONIO, TX 78223"                           
"3843 KILLARNEY SAN ANTONIO, TX 78223"                           
"1314 W HARLAN AVE SAN ANTONIO, TX 78211 UNITED STATES"          
"512 COUNTY ROAD 6717 NATALIA, TX 78059 UNITED STATES"           
"4609 ROSEWOOD DR MIDLAND, TEXAS 79707 UNITED STATES"            
"1120 S MARIPOSA AVE APT 5 LOS ANGELES, CA 90006"                
""                                                               
"E COMMERCE ST / HOEFGEN AVE SAN ANTONIO, TX 78205 UNITED STATES"
""                                                               
"11503 BEAR PAW PATH SAN ANTONIO, TX 78245"                      
"500 HARROLD ST FORT WORTH, TX 76107 UNITED STATES"              
"3602 PRINCE GEORGE DR SAN ANTONIO, TX 78230"                    
"3602 PRINCE GEORGE DR SAN ANTONIO, TX 78230"                    
"1611 BERNARD KERN DR EL PASO, TEXAS 79936 UNITED STATES"        
"1611 BERNARD KERN DR EL PASO, TEXAS 79936 UNITED STATES"        
"117 HARTE IRAAN, TEXAS 79744 UNITED STATES"                     
""                                                               
""                                                               
"5906 IMPERIAL TOPAZ SAN ANTONIO, TX 78222 UNITED STATES"        
""                                                               
"306 BICKLEY SAN ANTONIO, TX 78221 UNITED STATES"                
"1109 MADRID SAN ANTONIO, TX 78237 UNITED STATES"                
"1109 MADRID SAN ANTONIO, TX 78237 UNITED STATES"                
"2801 MEADOW VIEW COMMERCE, TX 75428 UNITED STATES"              
""                                                               
"202 ESTATE SAN ANTONIO, TX 78220 UNITED STATES"                 
"202 ESTATE SAN ANTONIO, TX 78220 UNITED STATES"                 
"202 ESTATE SAN ANTONIO, TX 78220 UNITED STATES"                 
"202 ESTATE SAN ANTONIO, TX 78220 UNITED STATES"                 
"7031 HEATHERS WAY SAN ANTONIO, TX 78227 UNITED STATES"          
"2432 W THIRD ST . MADERA, CALIFORNIA 93637 UNITED STATES"       
"2023 SHADOW CLIFF SAN ANTONIO, TX 78232 UNITED STATES"          
"2023 SHADOW CLIFF SAN ANTONIO, TX 78232 UNITED STATES"          
"219 VERBENA HILL SAN ANTONIO, TX 78258 UNITED STATES"           
"5511 HWY 71 BEE CAVE, TX 78738 UNITED STATES"                   
"10607 COUGAR CHASE SAN ANTONIO, TX 78251 UNITED STATES"         
"9210 MIMOSA MANOR SAN ANTONIO, TX 78245"                        
"6611 ARANCIONE AVE SAN ANTONIO, TX 78233 UNITED STATES"         
"175 HARTFORD SAN ANTONIO, TX 78223 UNITED STATES"               
"1095 SW COUNTY ROAD CHILHOWEE, MO 44733 UNITED STATES"          
"302 E PALM DR FRESNO, TEXAS 77545 UNITED STATES"                
"7314 WESTGLADE PLACE SAN ANTONIO, TX 78227 UNITED STATES"       
""                                                               
"1160 W HIGHWAY 85 DILLEY, TX 78017"                             
"315 MARTHAS LN SOMERSET, TEXAS 78069 UNITED STATES"             
""                                                               
"10314 CLEARWATER WAY SAN ANTONIO, TX 78223 UNITED STATES"       
"10444 GREEN BRANCH SAN ANTONIO, TEXAS 78223 UNITED STATES"      
"10314 CLEARWATER WAY SAN ANTONIO, TX 78223 UNITED STATES"       
"239 CENTER ST SAN ANTONIO, TX 78202 UNITED STATES"              
"239 CENTER ST SAN ANTONIO, TX 78202 UNITED STATES"              
"20064 FM 523 ANGLETON, TX 77515 UNITED STATES"                  
""                                                               
""                                                               
""                                                               
"6706 KINGSBURY DR DALLAS, TX 75201 UNITED STATES"               
end

[Andrew Musau]

I don't think you can automate the extraction of cities unless you have a dictionary with the names of all cities in your sample. Even then, some addresses may contain city names within them. You can easily extract the state and ZIP code using:

split PERSONALADDRESS, parse(,) gen(split)

There are several stopwords such as road, lane, drive, place, apartment, valley, and so on, which you can use. However, there will be some addresses that do not include these stopwords, e.g.,

8134 GRISSOM SAN ANTONIO, TX 78251

So, you may need to handle them manually.

[Hemanshu Kumar]

Here's something to at least separate out the state part. Consider:

local st_abbrevs CA MO RI TX
local states `" "CALIFORNIA" "MONTANA" "RHODE ISLAND" "TEXAS" "'
local num_states: word count `st_abbrevs'

gen street_bit = ""
gen state_bit = ""
gen byte done = 0

forval i=1/`num_states' {
    local st_ab: word `i' of `st_abbrevs'
    local state: word `i' of `states'
    
    local regex_str = ", ([`st_ab'|`state'].*)$"
    replace state_bit = ustrregexs(1) if ustrregexm(PERSONALADDRESS, "`regex_str'") & !done
    replace street_bit = ustrregexrf(PERSONALADDRESS, "`regex_str'", "") if state_bit != "" & !done
    replace done = 1 if state_bit != ""
}

assert done if PERSONALADDRESS != "" // CHECK ALL NON-EMPTY ADDRESSES HAVE BEEN SUCCESSFULLY PROCESSED
drop done

which produces, for your extract:

. list street_bit state_bit, noobs sep(0)

  +--------------------------------------------------------------------------+
  |                              street_bit                        state_bit |
  |--------------------------------------------------------------------------|
  |         29 BROKEN ARROW RD BRACKETVILLE           TX 78832 UNITED STATES |
  |                   189 EMPRESARIO DR UBC           TX 78253 UNITED STATES |
  |             305 E LEROY ST THREE RIVERS        TEXAS 78071 UNITED STATES |
  |                                                                          |
  |              276 SADDLE HORN DR BANDERA           TX 78003 UNITED STATES |
  |              756 PURPLE SAGE DR BANDERA           TX 78003 UNITED STATES |
  |           151 SHADOW VALLEY SAN ANTONIO           TX 78227 UNITED STATES |
  |                8134 GRISSOM SAN ANTONIO                         TX 78251 |
  |                                                                          |
  |                                                                          |
  |              1624 W SALINAS SAN ANTONIO           TX 78207 UNITED STATES |
  |             11035 INNER CYN SAN ANTONIO                         TX 78252 |
  |             11035 INNER CYN SAN ANTONIO                         TX 78252 |
  |             11035 INNER CYN SAN ANTONIO                         TX 78252 |
  |                 7938 HANGING BRANCH UBC           TX 78253 UNITED STATES |
  |                1203 TENBURY SAN ANTONIO                         TX 78253 |
  |                                                                          |
  |               213 DEL VALLE SAN ANTONIO                         TX 78207 |
  |               213 DEL VALLE SAN ANTONIO                         TX 78207 |
  |                  710 NOVELLA AVE ADKINS                         TX 78101 |
  |          13003 ESSEN FOREST SAN ANTONIO           TX 78023 UNITED STATES |
  |      7922 PARKLAND GREEN DR SAN ANTONIO           TX 78240 UNITED STATES |
  |                  5322 EBONY SAN ANTONIO                         TX 78228 |
  |                                                                          |
  |          3106 CLIMBING ROSE SAN ANTONIO           TX 78230 UNITED STATES |
  |                511 W BAYLOR SAN ANTONIO           TX 78204 UNITED STATES |
  |                511 W BAYLOR SAN ANTONIO           TX 78204 UNITED STATES |
  |                511 W BAYLOR SAN ANTONIO           TX 78204 UNITED STATES |
  |              5110 BEVERLY DR SAN ANGELO        TEXAS 76904 UNITED STATES |
  |              2915 HIGHCLIFF SAN ANTONIO                         TX 78218 |
  |             18595 HOLLYBRANCH CT PORTER        TEXAS 77365 UNITED STATES |
  |             18595 HOLLYBRANCH CT PORTER        TEXAS 77365 UNITED STATES |
  |             12606 MIDDLE LN SAN ANTONIO           TX 78217 UNITED STATES |
  |                   2193 CR 342 LA VERNIA           TX 78121 UNITED STATES |
  |              2815 BRIAFIELD SAN ANTONIO                               TX |
  |              2815 BRIAFIELD SAN ANTONIO                               TX |
  |              2815 BRIAFIELD SAN ANTONIO                               TX |
  |              2815 BRIAFIELD SAN ANTONIO                               TX |
  |                1630 CRYSTAL BRIDGES UBC           TX 78260 UNITED STATES |
  |                1630 CRYSTAL BRIDGES UBC           TX 78260 UNITED STATES |
  |                1630 CRYSTAL BRIDGES UBC           TX 78260 UNITED STATES |
  |             8726 DISCOVERY WAY CONVERSE                         TX 78109 |
  |                                                                          |
  |                                                                          |
  |              3843 KILLARNEY SAN ANTONIO                         TX 78223 |
  |              3843 KILLARNEY SAN ANTONIO                         TX 78223 |
  |           1314 W HARLAN AVE SAN ANTONIO           TX 78211 UNITED STATES |
  |            512 COUNTY ROAD 6717 NATALIA           TX 78059 UNITED STATES |
  |                4609 ROSEWOOD DR MIDLAND        TEXAS 79707 UNITED STATES |
  |   1120 S MARIPOSA AVE APT 5 LOS ANGELES                         CA 90006 |
  |                                                                          |
  | E COMMERCE ST / HOEFGEN AVE SAN ANTONIO           TX 78205 UNITED STATES |
  |                                                                          |
  |         11503 BEAR PAW PATH SAN ANTONIO                         TX 78245 |
  |               500 HARROLD ST FORT WORTH           TX 76107 UNITED STATES |
  |       3602 PRINCE GEORGE DR SAN ANTONIO                         TX 78230 |
  |       3602 PRINCE GEORGE DR SAN ANTONIO                         TX 78230 |
  |            1611 BERNARD KERN DR EL PASO        TEXAS 79936 UNITED STATES |
  |            1611 BERNARD KERN DR EL PASO        TEXAS 79936 UNITED STATES |
  |                         117 HARTE IRAAN        TEXAS 79744 UNITED STATES |
  |                                                                          |
  |                                                                          |
  |         5906 IMPERIAL TOPAZ SAN ANTONIO           TX 78222 UNITED STATES |
  |                                                                          |
  |                 306 BICKLEY SAN ANTONIO           TX 78221 UNITED STATES |
  |                 1109 MADRID SAN ANTONIO           TX 78237 UNITED STATES |
  |                 1109 MADRID SAN ANTONIO           TX 78237 UNITED STATES |
  |               2801 MEADOW VIEW COMMERCE           TX 75428 UNITED STATES |
  |                                                                          |
  |                  202 ESTATE SAN ANTONIO           TX 78220 UNITED STATES |
  |                  202 ESTATE SAN ANTONIO           TX 78220 UNITED STATES |
  |                  202 ESTATE SAN ANTONIO           TX 78220 UNITED STATES |
  |                  202 ESTATE SAN ANTONIO           TX 78220 UNITED STATES |
  |           7031 HEATHERS WAY SAN ANTONIO           TX 78227 UNITED STATES |
  |                2432 W THIRD ST . MADERA   CALIFORNIA 93637 UNITED STATES |
  |           2023 SHADOW CLIFF SAN ANTONIO           TX 78232 UNITED STATES |
  |           2023 SHADOW CLIFF SAN ANTONIO           TX 78232 UNITED STATES |
  |            219 VERBENA HILL SAN ANTONIO           TX 78258 UNITED STATES |
  |                    5511 HWY 71 BEE CAVE           TX 78738 UNITED STATES |
  |          10607 COUGAR CHASE SAN ANTONIO           TX 78251 UNITED STATES |
  |           9210 MIMOSA MANOR SAN ANTONIO                         TX 78245 |
  |          6611 ARANCIONE AVE SAN ANTONIO           TX 78233 UNITED STATES |
  |                175 HARTFORD SAN ANTONIO           TX 78223 UNITED STATES |
  |           1095 SW COUNTY ROAD CHILHOWEE           MO 44733 UNITED STATES |
  |                    302 E PALM DR FRESNO        TEXAS 77545 UNITED STATES |
  |        7314 WESTGLADE PLACE SAN ANTONIO           TX 78227 UNITED STATES |
  |                                                                          |
  |                1160 W HIGHWAY 85 DILLEY                         TX 78017 |
  |                 315 MARTHAS LN SOMERSET        TEXAS 78069 UNITED STATES |
  |                                                                          |
  |        10314 CLEARWATER WAY SAN ANTONIO           TX 78223 UNITED STATES |
  |          10444 GREEN BRANCH SAN ANTONIO        TEXAS 78223 UNITED STATES |
  |        10314 CLEARWATER WAY SAN ANTONIO           TX 78223 UNITED STATES |
  |               239 CENTER ST SAN ANTONIO           TX 78202 UNITED STATES |
  |               239 CENTER ST SAN ANTONIO           TX 78202 UNITED STATES |
  |                   20064 FM 523 ANGLETON           TX 77515 UNITED STATES |
  |                                                                          |
  |                                                                          |
  |                                                                          |
  |                6706 KINGSBURY DR DALLAS           TX 75201 UNITED STATES |
  +--------------------------------------------------------------------------+

You will, of course want to add in all the states and their abbreviations in the macro definitions.

The code assumes that the regularity I noticed in your extract holds for your entire dataset -- the part about the state always starts with the pattern comma-space-state.
Furthermore it assumes that the street address never has that pattern. The code CAN break in situations like the following:

clear
input str89 PERSONALADDRESS
"APT 55, RHODE ISLAND AVE, CA 90210"
end

where you would get:

  +---------------------------------------+
  | street~t                    state_bit |
  |---------------------------------------|
  |   APT 55   RHODE ISLAND AVE, CA 90210 |
  +---------------------------------------+

You'll have to see if this kind of thing actually happens in your data.

[Andrew Musau]

There are several stopwords such as road, lane, drive, place, apartment, valley, and so on, which you can use

On this, an example is the following:

split PERSONALADDRESS, parse(,) gen(split)
gen city =ustrregexra(split1, ".*\s\b(ST|LN|RD|AVE|WAY|DR|CYN|CLIFF|HILL)\b\s(.*)$", "$2") if ustrregexm(split1, "\b(ST|LN|RD|APT|AVE|WAY|DR|CYN|CLIFF|HILL)\b" )

Res.:

. l split1 city, sep(0)

     +-------------------------------------------------------------+
     |                                  split1                city |
     |-------------------------------------------------------------|
  1. |         29 BROKEN ARROW RD BRACKETVILLE        BRACKETVILLE |
  2. |                   189 EMPRESARIO DR UBC                 UBC |
  3. |             305 E LEROY ST THREE RIVERS        THREE RIVERS |
  4. |                                                             |
  5. |              276 SADDLE HORN DR BANDERA             BANDERA |
  6. |              756 PURPLE SAGE DR BANDERA             BANDERA |
  7. |           151 SHADOW VALLEY SAN ANTONIO                     |
  8. |                8134 GRISSOM SAN ANTONIO                     |
  9. |                                                             |
 10. |                                                             |
 11. |              1624 W SALINAS SAN ANTONIO                     |
 12. |             11035 INNER CYN SAN ANTONIO         SAN ANTONIO |
 13. |             11035 INNER CYN SAN ANTONIO         SAN ANTONIO |
 14. |             11035 INNER CYN SAN ANTONIO         SAN ANTONIO |
 15. |                 7938 HANGING BRANCH UBC                     |
 16. |                1203 TENBURY SAN ANTONIO                     |
 17. |                                                             |
 18. |               213 DEL VALLE SAN ANTONIO                     |
 19. |               213 DEL VALLE SAN ANTONIO                     |
 20. |                  710 NOVELLA AVE ADKINS              ADKINS |
 21. |          13003 ESSEN FOREST SAN ANTONIO                     |
 22. |      7922 PARKLAND GREEN DR SAN ANTONIO         SAN ANTONIO |
 23. |                  5322 EBONY SAN ANTONIO                     |
 24. |                                                             |
 25. |          3106 CLIMBING ROSE SAN ANTONIO                     |
 26. |                511 W BAYLOR SAN ANTONIO                     |
 27. |                511 W BAYLOR SAN ANTONIO                     |
 28. |                511 W BAYLOR SAN ANTONIO                     |
 29. |              5110 BEVERLY DR SAN ANGELO          SAN ANGELO |
 30. |              2915 HIGHCLIFF SAN ANTONIO                     |
 31. |             18595 HOLLYBRANCH CT PORTER                     |
 32. |             18595 HOLLYBRANCH CT PORTER                     |
 33. |             12606 MIDDLE LN SAN ANTONIO         SAN ANTONIO |
 34. |                   2193 CR 342 LA VERNIA                     |
 35. |              2815 BRIAFIELD SAN ANTONIO                     |
 36. |              2815 BRIAFIELD SAN ANTONIO                     |
 37. |              2815 BRIAFIELD SAN ANTONIO                     |
 38. |              2815 BRIAFIELD SAN ANTONIO                     |
 39. |                1630 CRYSTAL BRIDGES UBC                     |
 40. |                1630 CRYSTAL BRIDGES UBC                     |
 41. |                1630 CRYSTAL BRIDGES UBC                     |
 42. |             8726 DISCOVERY WAY CONVERSE            CONVERSE |
 43. |                                                             |
 44. |                                                             |
 45. |              3843 KILLARNEY SAN ANTONIO                     |
 46. |              3843 KILLARNEY SAN ANTONIO                     |
 47. |           1314 W HARLAN AVE SAN ANTONIO         SAN ANTONIO |
 48. |            512 COUNTY ROAD 6717 NATALIA                     |
 49. |                4609 ROSEWOOD DR MIDLAND             MIDLAND |
 50. |   1120 S MARIPOSA AVE APT 5 LOS ANGELES   APT 5 LOS ANGELES |
 51. |                                                             |
 52. | E COMMERCE ST / HOEFGEN AVE SAN ANTONIO         SAN ANTONIO |
 53. |                                                             |
 54. |         11503 BEAR PAW PATH SAN ANTONIO                     |
 55. |               500 HARROLD ST FORT WORTH          FORT WORTH |
 56. |       3602 PRINCE GEORGE DR SAN ANTONIO         SAN ANTONIO |
 57. |       3602 PRINCE GEORGE DR SAN ANTONIO         SAN ANTONIO |
 58. |            1611 BERNARD KERN DR EL PASO             EL PASO |
 59. |            1611 BERNARD KERN DR EL PASO             EL PASO |
 60. |                         117 HARTE IRAAN                     |
 61. |                                                             |
 62. |                                                             |
 63. |         5906 IMPERIAL TOPAZ SAN ANTONIO                     |
 64. |                                                             |
 65. |                 306 BICKLEY SAN ANTONIO                     |
 66. |                 1109 MADRID SAN ANTONIO                     |
 67. |                 1109 MADRID SAN ANTONIO                     |
 68. |               2801 MEADOW VIEW COMMERCE                     |
 69. |                                                             |
 70. |                  202 ESTATE SAN ANTONIO                     |
 71. |                  202 ESTATE SAN ANTONIO                     |
 72. |                  202 ESTATE SAN ANTONIO                     |
 73. |                  202 ESTATE SAN ANTONIO                     |
 74. |           7031 HEATHERS WAY SAN ANTONIO         SAN ANTONIO |
 75. |                2432 W THIRD ST . MADERA            . MADERA |
 76. |           2023 SHADOW CLIFF SAN ANTONIO         SAN ANTONIO |
 77. |           2023 SHADOW CLIFF SAN ANTONIO         SAN ANTONIO |
 78. |            219 VERBENA HILL SAN ANTONIO         SAN ANTONIO |
 79. |                    5511 HWY 71 BEE CAVE                     |
 80. |          10607 COUGAR CHASE SAN ANTONIO                     |
 81. |           9210 MIMOSA MANOR SAN ANTONIO                     |
 82. |          6611 ARANCIONE AVE SAN ANTONIO         SAN ANTONIO |
 83. |                175 HARTFORD SAN ANTONIO                     |
 84. |           1095 SW COUNTY ROAD CHILHOWEE                     |
 85. |                    302 E PALM DR FRESNO              FRESNO |
 86. |        7314 WESTGLADE PLACE SAN ANTONIO                     |
 87. |                                                             |
 88. |                1160 W HIGHWAY 85 DILLEY                     |
 89. |                 315 MARTHAS LN SOMERSET            SOMERSET |
 90. |                                                             |
 91. |        10314 CLEARWATER WAY SAN ANTONIO         SAN ANTONIO |
 92. |          10444 GREEN BRANCH SAN ANTONIO                     |
 93. |        10314 CLEARWATER WAY SAN ANTONIO         SAN ANTONIO |
 94. |               239 CENTER ST SAN ANTONIO         SAN ANTONIO |
 95. |               239 CENTER ST SAN ANTONIO         SAN ANTONIO |
 96. |                   20064 FM 523 ANGLETON                     |
 97. |                                                             |
 98. |                                                             |
 99. |                                                             |
100. |                6706 KINGSBURY DR DALLAS              DALLAS |
     +-------------------------------------------------------------+

[Hemanshu Kumar]

Ah, I see now that the only time commas appear in your string is where they separate out the state part. If this is true of your full data, then of course you can simply split the string as advised in #2, and ignore my method in #3.

[Andrew Musau]

Hello, I have the variable PERSONALADDRESS which a sample of is shown below, I want to create two new variables based on this address variable, one variable must contain the street address and another variable must contain the city, state. zipcode

By the way, if this were my task, I would just feed the addresses to the ChatBot and ask it to create a column or columns with the desired variables. There’s no need to spend time writing code for this. I tried it with a subset of your data and, as I had guessed, it worked quite well.

[Luis Mijares Castaneda]

By the way, if this were my task, I would just feed the addresses to the ChatBot and ask it to create a column or columns with the desired variables. There’s no need to spend time writing code for this. I tried it with a subset of your data and, as I had guessed, it worked quite well.

[ATTACH=CONFIG]n1758186[/ATTACH]

for some reason chatgpt is refusing to do this change for me, what did you ask it specifically?

[Andrew Musau]

I have GPT-4o. The question is: "Can you create a column with the city names from the following list of addresses?"