Substring extraction with multiple options

user_13628900 0
Viewed 105

I have a variable in Stata in my dataset that looks like this:

city
Washington city
Boston city
El Paso city
Nashville-Davidson metropolitan government (balance)
Lexington-Fayette urban county

And I want it to look like:

city
Washington
Boston
El Paso
Nashville-Davidson
Lexington-Fayette

"city," "county," and "urban county" are the only three words that follow after a city name. In other words, I want to extract the substring from left to the space before either city, county, or urban.

The only way I can think of approaching this using subinstring:

replace city = subinstr(city, " city", "", .)

I don't think, however, that I can add multiple options here.

stata fuzzy-comparison matching string
3 Answers

I used subinstr to replace the desired words with empty strings, and trim to remove additional spaces.

input str60(city)

"Washington city"
"Boston city"
"El Paso city"
"Lexington-Fayette urban county"
"Audacity"

end

gen     wanted = subinstr(city,"urban county","",1)
replace wanted = subinstr(wanted," county","",1)
replace wanted = subinstr(wanted," city","",1)

replace wanted = trim(wanted)

list

     +----------------------------------------------------+
     |                           city              wanted |
     |----------------------------------------------------|
  1. |                Washington city          Washington |
  2. |                    Boston city              Boston |
  3. |                   El Paso city             El Paso |
  4. | Lexington-Fayette urban county   Lexington-Fayette |
  5. |                       Audacity            Audacity |
     +----------------------------------------------------+

Edit: As suggested, I have incorporated a leading space so that places with "city" in their name (e.g. Audacity) are not inadvertently replaced. The same for "county" (although this seems less likely).

user_118274040

split could be a way.

split city, parse(" city" " urban" " county") limit(1)
user_90484120

I think using regular expression replacement to search for a space followed by a relevant substring would be the most flexible option here. For example:

clear
input str60(city)

"Washington city"
"Boston city"
"El Paso city"
"Lexington-Fayette urban county"
"Audacity"
"Salt Lake City city"

end

gen clean_city = ustrregexra(city, "\s(city|county|urban county)","")


     +----------------------------------------------------+
     |                           city          clean_city |
     |----------------------------------------------------|
  1. |                Washington city          Washington |
  2. |                    Boston city              Boston |
  3. |                   El Paso city             El Paso |
  4. | Lexington-Fayette urban county   Lexington-Fayette |
  5. |                       Audacity            Audacity |
  6. |            Salt Lake City city      Salt Lake City |
     +----------------------------------------------------+
user_131870910
Related