Some questions about separating strings and making separate variables

[Eddie Brighton]

Dear Statlist:

I'm working on a dataset that contains job announcements across the federal workforce. That is, the unit of analysis is job announcements, which has variables such as organization, date of position opened/closed, job title, location, and so on. My question here relates to my location variable. Due to the nature of the Federal workforce's nature that jobs are announced on a functional basis (rather than regionality), some (or most) announcements have more than one region hiring for the position. For example, the data currently looks something like the following.

Organization	Job title	...	Location
XX	YY	...	El Centro, CA, | Sacramento, CA, | Susanville, CA,
...	...	...	...

Here, three regions (El Centro, Sacramento, and Susanville) are hiring for the same position. This is a challenge for me to identifying how many and what types of announcements were posted by region. To look into a more granular level of regional analysis, I want to separate the regions, but at the same time, maintain other information. In this case, I want to make three identical rows with other variables the same except for the location. That would look something like ...

Organization	Job title	...	Location
XX	YY	...	El Centro, CA
XX	YY	...	Sacramento, CA
XX	YY	...	Susanville, CA
...	...	...	...

The regions are separated by "|" .

Relatedly, my second question: I want to make a state variable by extracting only the states from the current format (e.g., CA).

Can anyone help me out on these? Thanks.

My data looks like this.

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 agency str69 job_series_title strL announcement_locations str10(announcement_open_date announcement_close_date)
"Department of Defense"                   "Financial Administration And Program" "Pittsburgh, PA,"                                    "7/1/2020"   "7/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Tinker AFB, OK,"                                    "10/1/2018"  "10/9/2018" 
"Department of Health And Human Services" "Health System Specialist"             "Billings, MT,"                                      "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Security Clerical and Assistance"     "Nashville, TN,"                                     "10/1/2018"  "10/11/2018"
"Department of Homeland Security"         "Customs And Border Protection"        "Washington, DC,"                                    "6/8/2020"   "6/12/2020" 
"Department of Defense"                   "Financial Administration And Program" "Hickam AFB, HI,"                                    "6/4/2020"   "6/24/2020" 
"Department of Defense"                   "Sports Specialist"                    "Kirtland AFB, NM,"                                  "8/20/2020"  "8/26/2020" 
"Department of Veterans Affairs"          "Medical Instrument Technician"        "Durham, NC,"                                        "8/19/2020"  "9/3/2020"  
"Department of Defense"                   "Medical Instrument Technician"        "Fort Leonard Wood, MO,"                             "10/1/2018"  "10/9/2018" 
"Department of Defense"                   "Speech Pathology And Audiology"       "Tacoma, WA,"                                        "10/2/2018"  "10/9/2018" 
"Department of Veterans Affairs"          "Biological Science Technician"        "Buffalo, NY,"                                       "10/1/2018"  "10/12/2018"
"Department of Defense"                   "Transportation Operations"            "Dobbins AFB, GA,"                                   "10/24/2018" "10/30/2018"
"Small Business Administration"           "General Business And Industry"        "King of Prussia, PA,"                               "10/1/2018"  "10/12/2018"
"Department of Defense"                   "General Business And Industry"        "Fort Eustis, VA,"                                   "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Physician Assistant"                  "Iowa City, IA,"                                     "8/20/2020"  "9/3/2020"  
"Department of Defense"                   "General Business And Industry"        "Camp Pendleton, CA,"                                "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Financial Administration And Program" "Saint Paul, MN,"                                    "10/16/2018" "10/22/2018"
"Department of Defense"                   "General Business And Industry"        "Saint Louis, MO,"                                   "10/15/2018" "10/19/2018"
"Department of Health And Human Services" "Actuarial Science"                    "Woodlawn, MD,"                                      "10/1/2018"  "10/16/2018"
"Department of Defense"                   "Respiratory Therapist"                "Tripler Army Medical Center, HI,"                   "10/3/2018"  "10/11/2018"
"Department of Defense"                   "Transportation Operations"            "Sigonella Sicily, Italy"                            "10/2/2018"  "10/10/2018"
"Department of Defense"                   "Financial Administration And Program" "Indianapolis, IN,"                                  "6/3/2020"   "6/16/2020" 
"Department of Veterans Affairs"          "Medical Supply Aide And Technician"   "West Roxbury, MA,"                                  "6/3/2020"   "6/25/2020" 
"Department of Defense"                   "General Business And Industry"        "Fallon, NV,"                                        "6/3/2020"   "6/15/2020" 
"Department of Defense"                   "Financial Administration And Program" "Fort Detrick, MD,"                                  "10/1/2018"  "10/1/2018" 
"Department of Justice"                   "Financial Administration And Program" "Arlington, VA,"                                     "10/1/2018"  "10/10/2018"
"Department of Health And Human Services" "Biological Science Technician"        "Jefferson, AR,"                                     "7/29/2020"  "8/12/2020" 
"Department of Agriculture"               "General Business And Industry"        "Morrilton, AR,"                                     "7/30/2020"  "8/10/2020" 
"Department of Defense"                   "Financial Administration And Program" "Bethesda, MD,"                                      "8/5/2020"   "8/12/2020" 
"Department of Defense"                   "General Business And Industry"        "Naval Air Station Key West , FL,"                   "7/2/2020"   "7/8/2020"  
"Department of Veterans Affairs"          "Physician Assistant"                  "Reno, NV,"                                          "7/1/2020"   "7/21/2020" 
"Department of Defense"                   "Engineering Technical"                "Memphis, TN,"                                       "6/4/2020"   "6/12/2020" 
"Department of Veterans Affairs"          "Speech Pathology And Audiology"       "Wichita, KS,"                                       "6/5/2020"   "6/25/2020" 
"Department of Defense"                   "Records & Information Management"     "Fort Meade, MD,"                                    "10/15/2018" "10/25/2018"
"Department of Defense"                   "General Business And Industry"        "Fort Lee, VA,"                                      "10/16/2018" "10/23/2018"
"Department of Veterans Affairs"          "Health Physics"                       "Jamaica Plain, MA,"                                 "10/16/2018" "10/22/2018"
"Department of Veterans Affairs"          "Physician Assistant"                  "Wytheville, VA,"                                    "10/15/2018" "11/5/2018" 
"Department of Veterans Affairs"          "Speech Pathology And Audiology"       "Perry Point, MD,"                                   "8/19/2020"  "9/8/2020"  
"Department of Agriculture"               "General Business And Industry"        "Osceola, AR,"                                       "8/21/2020"  "8/31/2020" 
"Department of Defense"                   "General Business And Industry"        "Fort Meade, MD,"                                    "10/1/2018"  "10/11/2018"
"Department of Homeland Security"         "Customs And Border Protection"        "Falls Church, VA,"                                  "10/5/2018"  "10/18/2018"
"Department of Veterans Affairs"          "Medical Supply Aide And Technician"   "Fresno, CA,"                                        "10/1/2018"  "10/19/2018"
"Department of Defense"                   "Naval Architecture"                   "Pearl Harbor Naval Base, Oahu, HI,"                 "7/29/2020"  "7/31/2020" 
"Department of Defense"                   "Health System Specialist"             "Landstuhl, Germany"                                 "7/31/2020"  "8/4/2020"  
"Department of Health And Human Services" "Medical Supply Aide And Technician"   "Albuquerque, NM,"                                   "9/29/2020"  "10/13/2020"
"Department of Veterans Affairs"          "Health System Specialist"             "Danville, IL,"                                      "8/21/2020"  "8/27/2020" 
"Department of Veterans Affairs"          "Security Clerical and Assistance"     "Harlingen, TX,"                                     "8/24/2020"  "9/15/2020" 
"Department of Homeland Security"         "Customs And Border Protection"        "Laredo, TX,"                                        "8/31/2020"  "9/4/2020"  
"Department of Veterans Affairs"          "Biological Science Technician"        "Boise, ID,"                                         "10/23/2018" "10/29/2018"
"Department of Defense"                   "Physician Assistant"                  "Fort Carson, CO,"                                   "11/23/2018" "12/3/2018" 
"Department of Veterans Affairs"          "Psychology Aid And Technician"        "El Paso, TX,"                                       "7/4/2020"   "7/8/2020"  
"Department of Defense"                   "Financial Administration And Program" "Fort Belvoir, VA,"                                  "7/29/2020"  "8/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Schriever AFB, CO,"                                 "7/29/2020"  "8/4/2020"  
"Department of Defense"                   "Health System Specialist"             "MacDill AFB, FL,"                                   "6/8/2020"   "6/12/2020" 
"Department of Veterans Affairs"          "Health System Specialist"             "Detroit, MI,"                                       "10/22/2018" "11/1/2018" 
"Department of Defense"                   "Medical Instrument Technician"        "Fort Leonard Wood, MO,"                             "10/24/2018" "11/26/2018"
"Department of Defense"                   "General Business And Industry"        "Warren AFB, WY,"                                    "10/1/2018"  "10/15/2018"
"Department of Defense"                   "Biological Science Technician"        "Air Force Academy, CO,"                             "10/19/2018" "11/9/2018" 
"Small Business Administration"           "General Business And Industry"        "King of Prussia, PA,"                               "10/8/2018"  "10/12/2018"
"Department of Defense"                   "Health Physics"                       "Crane, IN,"                                         "10/1/2018"  "10/2/2018" 
"Department of Defense"                   "Financial Administration And Program" "Wright-Patterson AFB, OH,"                          "10/10/2018" "10/16/2018"
"Department of Defense"                   "General Business And Industry"        "Robins AFB, GA,"                                    "7/7/2020"   "7/13/2020" 
"Department of Defense"                   "General Business And Industry"        "Redstone Arsenal, AL,"                              "7/8/2020"   "7/22/2020" 
"Department of Defense"                   "Financial Administration And Program" "New Cumberland Defense Logistics Center, PA,"       "7/31/2020"  "8/10/2020" 
"Department of Health And Human Services" "General Business And Industry"        "Montgomery County, MD,"                             "8/3/2020"   "8/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Watervliet, NY,"                                    "10/3/2018"  "10/12/2018"
"Department of the Interior"              "Archeology"                           "El Centro, CA, | Sacramento, CA, | Susanville, CA," "8/19/2020"  "9/7/2020"  
"Department of Defense"                   "Fire Protection and Prevention"       "Buckley AFB, CO,"                                   "8/19/2020"  "8/26/2020" 
"Department of Defense"                   "Social Science"                       "Marine Corps Air Station Miramar, CA,"              "8/20/2020"  "8/28/2020" 
"Department of Homeland Security"         "Program Management"                   "Imperial, CA,"                                      "7/30/2020"  "8/12/2020" 
"Department of Defense"                   "Rehabilitation Therapy Assistant"     "Fort Polk, LA,"                                     "7/31/2020"  "8/6/2020"  
"Department of Defense"                   "Inventory Management"                 "Hickam AFB, HI,"                                    "10/2/2018"  "10/9/2018" 
"Department of Veterans Affairs"          "Clinical Laboratory Science"          "Chicago, IL,"                                       "10/15/2018" "11/2/2018" 
"Department of Defense"                   "Financial Clerical And Assistance"    "Millington, TN,"                                    "10/1/2018"  "10/9/2018" 
"Department of the Interior"              "Program Management"                   "Salt Lake City, UT,"                                "6/3/2020"   "6/16/2020" 
"Department of Defense"                   "Social Science"                       "Fort Wainwright, AK,"                               "6/4/2020"   "6/11/2020" 
"Department of Defense"                   "Program Management"                   "Pearl Harbor Naval Base, Oahu, HI,"                 "7/1/2020"   "7/6/2020"  
"Department of Defense"                   "Social Science"                       "Ramstein, Germany"                                  "7/2/2020"   "7/9/2020"  
"Department of Defense"                   "Instructional Systems"                "Monterey, CA,"                                      "7/1/2020"   "7/15/2020" 
"Department of Defense"                   "Fire Protection and Prevention"       "Fort Bliss, TX,"                                    "6/8/2020"   "6/19/2020" 
"Department of Defense"                   "Property Disposal"                    "Herlong Sierra Ordnance Depot, CA,"                 "8/19/2020"  "8/28/2020" 
"Small Business Administration"           "Program Management"                   "Citrus Heights, CA,"                                "8/21/2020"  "8/27/2020" 
"Department of Veterans Affairs"          "Clinical Laboratory Science"          "Fargo, ND,"                                         "8/20/2020"  "9/9/2020"  
"Department of Defense"                   "Procurement Clerical And Technician"  "Santa Ana, CA,"                                     "10/15/2018" "10/25/2018"
"Department of Defense"                   "Property Disposal"                    "Fort Bragg, NC,"                                    "10/1/2018"  "10/11/2018"
"Department of Defense"                   "Construction Control Technical"       "Schinnen, Netherlands"                              "7/29/2020"  "8/10/2020" 
"Department of Veterans Affairs"          "Social Science"                       "Marion, IL,"                                        "7/29/2020"  "8/12/2020" 
"Department of Defense"                   "Dental Assistant"                     "Fort Carson, CO,"                                   "7/6/2020"   "7/13/2020" 
"Department of Defense"                   "Program Management"                   "Brussels, Belgium"                                  "7/2/2020"   "7/8/2020"  
"Department of Agriculture"               "Program Management"                   "Washington, DC,"                                    "6/3/2020"   "6/9/2020"  
"Department of Veterans Affairs"          "Pharmacist"                           "Salem, VA,"                                         "8/13/2020"  "9/8/2020"  
"Department of Agriculture"               "Social Science"                       "Harrisburg, IL,"                                    "8/26/2020"  "9/15/2020" 
"Department of Veterans Affairs"          "Inventory Management"                 "San Juan, PR,"                                      "8/20/2020"  "8/31/2020" 
"Department of Defense"                   "Social Science"                       "McGuire AFB, NJ,"                                   "6/4/2020"   "6/10/2020" 
"Department of Defense"                   "Dental Assistant"                     "Joint Base Lewis-McChord, WA,"                      "6/4/2020"   "6/19/2020" 
"Department of Veterans Affairs"          "Social Science"                       "Fargo, ND,"                                         "7/28/2020"  "8/25/2020" 
"Department of Defense"                   "Construction Control Technical"       "Dulmen, Germany"                                    "7/29/2020"  "8/10/2020" 
"Department of Defense"                   "Construction Control Technical"       "Stockton, CA, | West Sacramento, CA,"               "8/25/2020"  "9/7/2020"  
"Department of Veterans Affairs"          "Program Management"                   "Washington DC,"                                     "10/15/2018" "10/17/2018"
"Department of Defense"                   "Social Science"                       "Oklahoma City, OK,"                                 "7/1/2020"   "7/10/2020" 
end

------------------ copy up to and including the previous line ------------------

[Nick Cox]

Here is a start, and you can see that some problems remain:

keep announcement_locations
gen long obsno = _n 
rename announcement_locations location
split location, parse("|") 
drop location 
reshape long location, i(obsno) j(which)
drop if location == "" 
replace location = trim(location)
replace location = substr(location, 1, strlen(location) - 1) if substr(location, -1, 1) == ","
split location , parse(",") 
local J = r(k_new)

forval j = 3/`J' {
replace location2 = location2 + "," + location`j' if location`j' != "" 
} 

list location1 location2 

     +--------------------------------------------------------+
     |                               location1      location2 |
     |--------------------------------------------------------|
  1. |                              Pittsburgh             PA |
  2. |                              Tinker AFB             OK |
  3. |                                Billings             MT |
  4. |                               Nashville             TN |
  5. |                              Washington             DC |
     |--------------------------------------------------------|
  6. |                              Hickam AFB             HI |
  7. |                            Kirtland AFB             NM |
  8. |                                  Durham             NC |
  9. |                       Fort Leonard Wood             MO |
 10. |                                  Tacoma             WA |
     |--------------------------------------------------------|
 11. |                                 Buffalo             NY |
 12. |                             Dobbins AFB             GA |
 13. |                         King of Prussia             PA |
 14. |                             Fort Eustis             VA |
 15. |                               Iowa City             IA |
     |--------------------------------------------------------|
 16. |                          Camp Pendleton             CA |
 17. |                              Saint Paul             MN |
 18. |                             Saint Louis             MO |
 19. |                                Woodlawn             MD |
 20. |             Tripler Army Medical Center             HI |
     |--------------------------------------------------------|
 21. |                        Sigonella Sicily          Italy |
 22. |                            Indianapolis             IN |
 23. |                            West Roxbury             MA |
 24. |                                  Fallon             NV |
 25. |                            Fort Detrick             MD |
     |--------------------------------------------------------|
 26. |                               Arlington             VA |
 27. |                               Jefferson             AR |
 28. |                               Morrilton             AR |
 29. |                                Bethesda             MD |
 30. |             Naval Air Station Key West              FL |
     |--------------------------------------------------------|
 31. |                                    Reno             NV |
 32. |                                 Memphis             TN |
 33. |                                 Wichita             KS |
 34. |                              Fort Meade             MD |
 35. |                                Fort Lee             VA |
     |--------------------------------------------------------|
 36. |                           Jamaica Plain             MA |
 37. |                              Wytheville             VA |
 38. |                             Perry Point             MD |
 39. |                                 Osceola             AR |
 40. |                              Fort Meade             MD |
     |--------------------------------------------------------|
 41. |                            Falls Church             VA |
 42. |                                  Fresno             CA |
 43. |                 Pearl Harbor Naval Base       Oahu, HI |
 44. |                               Landstuhl        Germany |
 45. |                             Albuquerque             NM |
     |--------------------------------------------------------|
 46. |                                Danville             IL |
 47. |                               Harlingen             TX |
 48. |                                  Laredo             TX |
 49. |                                   Boise             ID |
 50. |                             Fort Carson             CO |
     |--------------------------------------------------------|
 51. |                                 El Paso             TX |
 52. |                            Fort Belvoir             VA |
 53. |                           Schriever AFB             CO |
 54. |                             MacDill AFB             FL |
 55. |                                 Detroit             MI |
     |--------------------------------------------------------|
 56. |                       Fort Leonard Wood             MO |
 57. |                              Warren AFB             WY |
 58. |                       Air Force Academy             CO |
 59. |                         King of Prussia             PA |
 60. |                                   Crane             IN |
     |--------------------------------------------------------|
 61. |                    Wright-Patterson AFB             OH |
 62. |                              Robins AFB             GA |
 63. |                        Redstone Arsenal             AL |
 64. | New Cumberland Defense Logistics Center             PA |
 65. |                       Montgomery County             MD |
     |--------------------------------------------------------|
 66. |                              Watervliet             NY |
 67. |                               El Centro             CA |
 68. |                              Sacramento             CA |
 69. |                              Susanville             CA |
 70. |                             Buckley AFB             CO |
     |--------------------------------------------------------|
 71. |        Marine Corps Air Station Miramar             CA |
 72. |                                Imperial             CA |
 73. |                               Fort Polk             LA |
 74. |                              Hickam AFB             HI |
 75. |                                 Chicago             IL |
     |--------------------------------------------------------|
 76. |                              Millington             TN |
 77. |                          Salt Lake City             UT |
 78. |                         Fort Wainwright             AK |
 79. |                 Pearl Harbor Naval Base       Oahu, HI |
 80. |                                Ramstein        Germany |
     |--------------------------------------------------------|
 81. |                                Monterey             CA |
 82. |                              Fort Bliss             TX |
 83. |           Herlong Sierra Ordnance Depot             CA |
 84. |                          Citrus Heights             CA |
 85. |                                   Fargo             ND |
     |--------------------------------------------------------|
 86. |                               Santa Ana             CA |
 87. |                              Fort Bragg             NC |
 88. |                                Schinnen    Netherlands |
 89. |                                  Marion             IL |
 90. |                             Fort Carson             CO |
     |--------------------------------------------------------|
 91. |                                Brussels        Belgium |
 92. |                              Washington             DC |
 93. |                                   Salem             VA |
 94. |                              Harrisburg             IL |
 95. |                                San Juan             PR |
     |--------------------------------------------------------|
 96. |                             McGuire AFB             NJ |
 97. |                Joint Base Lewis-McChord             WA |
 98. |                                   Fargo             ND |
 99. |                                  Dulmen        Germany |
100. |                                Stockton             CA |
     |--------------------------------------------------------|
101. |                         West Sacramento             CA |
102. |                           Washington DC                |
103. |                           Oklahoma City             OK |
     +--------------------------------------------------------+

[Andrew Musau]

Here is another way using expand and regular expressions.

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 agency str69 job_series_title strL announcement_locations str10(announcement_open_date announcement_close_date)
"Department of Defense"                   "Financial Administration And Program" "Pittsburgh, PA,"                                    "7/1/2020"   "7/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Tinker AFB, OK,"                                    "10/1/2018"  "10/9/2018" 
"Department of Health And Human Services" "Health System Specialist"             "Billings, MT,"                                      "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Security Clerical and Assistance"     "Nashville, TN,"                                     "10/1/2018"  "10/11/2018"
"Department of Homeland Security"         "Customs And Border Protection"        "Washington, DC,"                                    "6/8/2020"   "6/12/2020" 
"Department of Defense"                   "Financial Administration And Program" "Hickam AFB, HI,"                                    "6/4/2020"   "6/24/2020" 
"Department of Defense"                   "Sports Specialist"                    "Kirtland AFB, NM,"                                  "8/20/2020"  "8/26/2020" 
"Department of Veterans Affairs"          "Medical Instrument Technician"        "Durham, NC,"                                        "8/19/2020"  "9/3/2020"  
"Department of Defense"                   "Medical Instrument Technician"        "Fort Leonard Wood, MO,"                             "10/1/2018"  "10/9/2018" 
"Department of Defense"                   "Speech Pathology And Audiology"       "Tacoma, WA,"                                        "10/2/2018"  "10/9/2018" 
"Department of Veterans Affairs"          "Biological Science Technician"        "Buffalo, NY,"                                       "10/1/2018"  "10/12/2018"
"Department of Defense"                   "Transportation Operations"            "Dobbins AFB, GA,"                                   "10/24/2018" "10/30/2018"
"Small Business Administration"           "General Business And Industry"        "King of Prussia, PA,"                               "10/1/2018"  "10/12/2018"
"Department of Defense"                   "General Business And Industry"        "Fort Eustis, VA,"                                   "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Physician Assistant"                  "Iowa City, IA,"                                     "8/20/2020"  "9/3/2020"  
"Department of Defense"                   "General Business And Industry"        "Camp Pendleton, CA,"                                "10/1/2018"  "10/15/2018"
"Department of Veterans Affairs"          "Financial Administration And Program" "Saint Paul, MN,"                                    "10/16/2018" "10/22/2018"
"Department of Defense"                   "General Business And Industry"        "Saint Louis, MO,"                                   "10/15/2018" "10/19/2018"
"Department of Health And Human Services" "Actuarial Science"                    "Woodlawn, MD,"                                      "10/1/2018"  "10/16/2018"
"Department of Defense"                   "Respiratory Therapist"                "Tripler Army Medical Center, HI,"                   "10/3/2018"  "10/11/2018"
"Department of Defense"                   "Transportation Operations"            "Sigonella Sicily, Italy"                            "10/2/2018"  "10/10/2018"
"Department of Defense"                   "Financial Administration And Program" "Indianapolis, IN,"                                  "6/3/2020"   "6/16/2020" 
"Department of Veterans Affairs"          "Medical Supply Aide And Technician"   "West Roxbury, MA,"                                  "6/3/2020"   "6/25/2020" 
"Department of Defense"                   "General Business And Industry"        "Fallon, NV,"                                        "6/3/2020"   "6/15/2020" 
"Department of Defense"                   "Financial Administration And Program" "Fort Detrick, MD,"                                  "10/1/2018"  "10/1/2018" 
"Department of Justice"                   "Financial Administration And Program" "Arlington, VA,"                                     "10/1/2018"  "10/10/2018"
"Department of Health And Human Services" "Biological Science Technician"        "Jefferson, AR,"                                     "7/29/2020"  "8/12/2020" 
"Department of Agriculture"               "General Business And Industry"        "Morrilton, AR,"                                     "7/30/2020"  "8/10/2020" 
"Department of Defense"                   "Financial Administration And Program" "Bethesda, MD,"                                      "8/5/2020"   "8/12/2020" 
"Department of Defense"                   "General Business And Industry"        "Naval Air Station Key West , FL,"                   "7/2/2020"   "7/8/2020"  
"Department of Veterans Affairs"          "Physician Assistant"                  "Reno, NV,"                                          "7/1/2020"   "7/21/2020" 
"Department of Defense"                   "Engineering Technical"                "Memphis, TN,"                                       "6/4/2020"   "6/12/2020" 
"Department of Veterans Affairs"          "Speech Pathology And Audiology"       "Wichita, KS,"                                       "6/5/2020"   "6/25/2020" 
"Department of Defense"                   "Records & Information Management"     "Fort Meade, MD,"                                    "10/15/2018" "10/25/2018"
"Department of Defense"                   "General Business And Industry"        "Fort Lee, VA,"                                      "10/16/2018" "10/23/2018"
"Department of Veterans Affairs"          "Health Physics"                       "Jamaica Plain, MA,"                                 "10/16/2018" "10/22/2018"
"Department of Veterans Affairs"          "Physician Assistant"                  "Wytheville, VA,"                                    "10/15/2018" "11/5/2018" 
"Department of Veterans Affairs"          "Speech Pathology And Audiology"       "Perry Point, MD,"                                   "8/19/2020"  "9/8/2020"  
"Department of Agriculture"               "General Business And Industry"        "Osceola, AR,"                                       "8/21/2020"  "8/31/2020" 
"Department of Defense"                   "General Business And Industry"        "Fort Meade, MD,"                                    "10/1/2018"  "10/11/2018"
"Department of Homeland Security"         "Customs And Border Protection"        "Falls Church, VA,"                                  "10/5/2018"  "10/18/2018"
"Department of Veterans Affairs"          "Medical Supply Aide And Technician"   "Fresno, CA,"                                        "10/1/2018"  "10/19/2018"
"Department of Defense"                   "Naval Architecture"                   "Pearl Harbor Naval Base, Oahu, HI,"                 "7/29/2020"  "7/31/2020" 
"Department of Defense"                   "Health System Specialist"             "Landstuhl, Germany"                                 "7/31/2020"  "8/4/2020"  
"Department of Health And Human Services" "Medical Supply Aide And Technician"   "Albuquerque, NM,"                                   "9/29/2020"  "10/13/2020"
"Department of Veterans Affairs"          "Health System Specialist"             "Danville, IL,"                                      "8/21/2020"  "8/27/2020" 
"Department of Veterans Affairs"          "Security Clerical and Assistance"     "Harlingen, TX,"                                     "8/24/2020"  "9/15/2020" 
"Department of Homeland Security"         "Customs And Border Protection"        "Laredo, TX,"                                        "8/31/2020"  "9/4/2020"  
"Department of Veterans Affairs"          "Biological Science Technician"        "Boise, ID,"                                         "10/23/2018" "10/29/2018"
"Department of Defense"                   "Physician Assistant"                  "Fort Carson, CO,"                                   "11/23/2018" "12/3/2018" 
"Department of Veterans Affairs"          "Psychology Aid And Technician"        "El Paso, TX,"                                       "7/4/2020"   "7/8/2020"  
"Department of Defense"                   "Financial Administration And Program" "Fort Belvoir, VA,"                                  "7/29/2020"  "8/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Schriever AFB, CO,"                                 "7/29/2020"  "8/4/2020"  
"Department of Defense"                   "Health System Specialist"             "MacDill AFB, FL,"                                   "6/8/2020"   "6/12/2020" 
"Department of Veterans Affairs"          "Health System Specialist"             "Detroit, MI,"                                       "10/22/2018" "11/1/2018" 
"Department of Defense"                   "Medical Instrument Technician"        "Fort Leonard Wood, MO,"                             "10/24/2018" "11/26/2018"
"Department of Defense"                   "General Business And Industry"        "Warren AFB, WY,"                                    "10/1/2018"  "10/15/2018"
"Department of Defense"                   "Biological Science Technician"        "Air Force Academy, CO,"                             "10/19/2018" "11/9/2018" 
"Small Business Administration"           "General Business And Industry"        "King of Prussia, PA,"                               "10/8/2018"  "10/12/2018"
"Department of Defense"                   "Health Physics"                       "Crane, IN,"                                         "10/1/2018"  "10/2/2018" 
"Department of Defense"                   "Financial Administration And Program" "Wright-Patterson AFB, OH,"                          "10/10/2018" "10/16/2018"
"Department of Defense"                   "General Business And Industry"        "Robins AFB, GA,"                                    "7/7/2020"   "7/13/2020" 
"Department of Defense"                   "General Business And Industry"        "Redstone Arsenal, AL,"                              "7/8/2020"   "7/22/2020" 
"Department of Defense"                   "Financial Administration And Program" "New Cumberland Defense Logistics Center, PA,"       "7/31/2020"  "8/10/2020" 
"Department of Health And Human Services" "General Business And Industry"        "Montgomery County, MD,"                             "8/3/2020"   "8/7/2020"  
"Department of Defense"                   "Engineering Technical"                "Watervliet, NY,"                                    "10/3/2018"  "10/12/2018"
"Department of the Interior"              "Archeology"                           "El Centro, CA, | Sacramento, CA, | Susanville, CA," "8/19/2020"  "9/7/2020"  
"Department of Defense"                   "Fire Protection and Prevention"       "Buckley AFB, CO,"                                   "8/19/2020"  "8/26/2020" 
"Department of Defense"                   "Social Science"                       "Marine Corps Air Station Miramar, CA,"              "8/20/2020"  "8/28/2020" 
"Department of Homeland Security"         "Program Management"                   "Imperial, CA,"                                      "7/30/2020"  "8/12/2020" 
"Department of Defense"                   "Rehabilitation Therapy Assistant"     "Fort Polk, LA,"                                     "7/31/2020"  "8/6/2020"  
"Department of Defense"                   "Inventory Management"                 "Hickam AFB, HI,"                                    "10/2/2018"  "10/9/2018" 
"Department of Veterans Affairs"          "Clinical Laboratory Science"          "Chicago, IL,"                                       "10/15/2018" "11/2/2018" 
"Department of Defense"                   "Financial Clerical And Assistance"    "Millington, TN,"                                    "10/1/2018"  "10/9/2018" 
"Department of the Interior"              "Program Management"                   "Salt Lake City, UT,"                                "6/3/2020"   "6/16/2020" 
"Department of Defense"                   "Social Science"                       "Fort Wainwright, AK,"                               "6/4/2020"   "6/11/2020" 
"Department of Defense"                   "Program Management"                   "Pearl Harbor Naval Base, Oahu, HI,"                 "7/1/2020"   "7/6/2020"  
"Department of Defense"                   "Social Science"                       "Ramstein, Germany"                                  "7/2/2020"   "7/9/2020"  
"Department of Defense"                   "Instructional Systems"                "Monterey, CA,"                                      "7/1/2020"   "7/15/2020" 
"Department of Defense"                   "Fire Protection and Prevention"       "Fort Bliss, TX,"                                    "6/8/2020"   "6/19/2020" 
"Department of Defense"                   "Property Disposal"                    "Herlong Sierra Ordnance Depot, CA,"                 "8/19/2020"  "8/28/2020" 
"Small Business Administration"           "Program Management"                   "Citrus Heights, CA,"                                "8/21/2020"  "8/27/2020" 
"Department of Veterans Affairs"          "Clinical Laboratory Science"          "Fargo, ND,"                                         "8/20/2020"  "9/9/2020"  
"Department of Defense"                   "Procurement Clerical And Technician"  "Santa Ana, CA,"                                     "10/15/2018" "10/25/2018"
"Department of Defense"                   "Property Disposal"                    "Fort Bragg, NC,"                                    "10/1/2018"  "10/11/2018"
"Department of Defense"                   "Construction Control Technical"       "Schinnen, Netherlands"                              "7/29/2020"  "8/10/2020" 
"Department of Veterans Affairs"          "Social Science"                       "Marion, IL,"                                        "7/29/2020"  "8/12/2020" 
"Department of Defense"                   "Dental Assistant"                     "Fort Carson, CO,"                                   "7/6/2020"   "7/13/2020" 
"Department of Defense"                   "Program Management"                   "Brussels, Belgium"                                  "7/2/2020"   "7/8/2020"  
"Department of Agriculture"               "Program Management"                   "Washington, DC,"                                    "6/3/2020"   "6/9/2020"  
"Department of Veterans Affairs"          "Pharmacist"                           "Salem, VA,"                                         "8/13/2020"  "9/8/2020"  
"Department of Agriculture"               "Social Science"                       "Harrisburg, IL,"                                    "8/26/2020"  "9/15/2020" 
"Department of Veterans Affairs"          "Inventory Management"                 "San Juan, PR,"                                      "8/20/2020"  "8/31/2020" 
"Department of Defense"                   "Social Science"                       "McGuire AFB, NJ,"                                   "6/4/2020"   "6/10/2020" 
"Department of Defense"                   "Dental Assistant"                     "Joint Base Lewis-McChord, WA,"                      "6/4/2020"   "6/19/2020" 
"Department of Veterans Affairs"          "Social Science"                       "Fargo, ND,"                                         "7/28/2020"  "8/25/2020" 
"Department of Defense"                   "Construction Control Technical"       "Dulmen, Germany"                                    "7/29/2020"  "8/10/2020" 
"Department of Defense"                   "Construction Control Technical"       "Stockton, CA, | West Sacramento, CA,"               "8/25/2020"  "9/7/2020"  
"Department of Veterans Affairs"          "Program Management"                   "Washington DC,"                                     "10/15/2018" "10/17/2018"
"Department of Defense"                   "Social Science"                       "Oklahoma City, OK,"                                 "7/1/2020"   "7/10/2020" 
end

gen toexpand= length(announcement_locations)- length(subinstr(announcement_locations, "|", "",.))+1
expand toexpand
local regex
qui sum toexpand
forval i=1/`=`r(max)'+1'{
    local regex `regex'(.*)\|
}
bys agency job_series_title announcement_locations announcement_open_date announcement_close_date: gen which=_n
bys agency job_series_title announcement_locations announcement_open_date announcement_close_date: gen fullname=ustrregexs(which) if ustrregexm(announcement_locations, substr("`regex'", 1, (toexpand*6)-2))
replace fullname= ustrregexra(trim(itrim(fullname)), "(.*)(\,$)", "$1")
gen state= ustrregexs(1) if ustrregexm(fullname,",([^,]+$)")

Res.:

     +------------------------------------------------------------+
     |                                    fullname          state |
     |------------------------------------------------------------|
  1. |                               Morrilton, AR             AR |
  2. |                                 Osceola, AR             AR |
  3. |                              Washington, DC             DC |
  4. |                              Harrisburg, IL             IL |
  5. |                       Air Force Academy, CO             CO |
  6. |                             Dulmen, Germany        Germany |
  7. |                       Schinnen, Netherlands    Netherlands |
  8. |                                Stockton, CA             CA |
  9. |                         West Sacramento, CA             CA |
 10. |                             Fort Carson, CO             CO |
 11. |                Joint Base Lewis-McChord, WA             WA |
 12. |                                 Memphis, TN             TN |
 13. |                           Schriever AFB, CO             CO |
 14. |                              Tinker AFB, OK             OK |
 15. |                              Watervliet, NY             NY |
 16. |                                Bethesda, MD             MD |
 17. |                            Fort Belvoir, VA             VA |
 18. |                            Fort Detrick, MD             MD |
 19. |                              Hickam AFB, HI             HI |
 20. |                            Indianapolis, IN             IN |
 21. | New Cumberland Defense Logistics Center, PA             PA |
 22. |                              Pittsburgh, PA             PA |
 23. |                    Wright-Patterson AFB, OH             OH |
 24. |                              Millington, TN             TN |
 25. |                             Buckley AFB, CO             CO |
 26. |                              Fort Bliss, TX             TX |
 27. |                          Camp Pendleton, CA             CA |
 28. |                                  Fallon, NV             NV |
 29. |                             Fort Eustis, VA             VA |
 30. |                                Fort Lee, VA             VA |
 31. |                              Fort Meade, MD             MD |
 32. |             Naval Air Station Key West , FL             FL |
 33. |                        Redstone Arsenal, AL             AL |
 34. |                              Robins AFB, GA             GA |
 35. |                             Saint Louis, MO             MO |
 36. |                              Warren AFB, WY             WY |
 37. |                                   Crane, IN             IN |
 38. |                          Landstuhl, Germany        Germany |
 39. |                             MacDill AFB, FL             FL |
 40. |                                Monterey, CA             CA |
 41. |                              Hickam AFB, HI             HI |
 42. |                       Fort Leonard Wood, MO             MO |
 43. |                       Fort Leonard Wood, MO             MO |
 44. |           Pearl Harbor Naval Base, Oahu, HI             HI |
 45. |                             Fort Carson, CO             CO |
 46. |                               Santa Ana, CA             CA |
 47. |                           Brussels, Belgium        Belgium |
 48. |           Pearl Harbor Naval Base, Oahu, HI             HI |
 49. |                              Fort Bragg, NC             NC |
 50. |           Herlong Sierra Ordnance Depot, CA             CA |
 51. |                              Fort Meade, MD             MD |
 52. |                               Fort Polk, LA             LA |
 53. |             Tripler Army Medical Center, HI             HI |
 54. |                         Fort Wainwright, AK             AK |
 55. |        Marine Corps Air Station Miramar, CA             CA |
 56. |                             McGuire AFB, NJ             NJ |
 57. |                           Oklahoma City, OK             OK |
 58. |                           Ramstein, Germany        Germany |
 59. |                                  Tacoma, WA             WA |
 60. |                            Kirtland AFB, NM             NM |
 61. |                             Dobbins AFB, GA             GA |
 62. |                     Sigonella Sicily, Italy          Italy |
 63. |                                Woodlawn, MD             MD |
 64. |                               Jefferson, AR             AR |
 65. |                       Montgomery County, MD             MD |
 66. |                                Billings, MT             MT |
 67. |                             Albuquerque, NM             NM |
 68. |                            Falls Church, VA             VA |
 69. |                                  Laredo, TX             TX |
 70. |                              Washington, DC             DC |
 71. |                                Imperial, CA             CA |
 72. |                               Arlington, VA             VA |
 73. |                                   Boise, ID             ID |
 74. |                                 Buffalo, NY             NY |
 75. |                                 Chicago, IL             IL |
 76. |                                   Fargo, ND             ND |
 77. |                              Saint Paul, MN             MN |
 78. |                           Jamaica Plain, MA             MA |
 79. |                                Danville, IL             IL |
 80. |                                 Detroit, MI             MI |
 81. |                                San Juan, PR             PR |
 82. |                                  Durham, NC             NC |
 83. |                                  Fresno, CA             CA |
 84. |                            West Roxbury, MA             MA |
 85. |                                   Salem, VA             VA |
 86. |                               Iowa City, IA             IA |
 87. |                                    Reno, NV             NV |
 88. |                              Wytheville, VA             VA |
 89. |                               Washington DC                |
 90. |                                 El Paso, TX             TX |
 91. |                               Harlingen, TX             TX |
 92. |                               Nashville, TN             TN |
 93. |                                   Fargo, ND             ND |
 94. |                                  Marion, IL             IL |
 95. |                             Perry Point, MD             MD |
 96. |                                 Wichita, KS             KS |
 97. |                               El Centro, CA             CA |
 98. |                              Sacramento, CA             CA |
 99. |                              Susanville, CA             CA |
100. |                          Salt Lake City, UT             UT |
101. |                         King of Prussia, PA             PA |
102. |                         King of Prussia, PA             PA |
103. |                          Citrus Heights, CA             CA |
     +------------------------------------------------------------+

[Eddie Brighton]

Nick Cox Thanks. I'm running your codes. While waiting... what do you mean by the "remaining problems" to this approach?

[Nick Cox]

Well, with my code

"Washington DC" won't get parsed as you would want. (True of Andrew Musau's code as well.)

"Oahu, HI" may not be parsed the way you want. (Andrew's code is, I guess, closer what you want.)

"Sicily Italy" may not be parsed the way you want. (Andrew's code is, I guess, closer what you want.)

"Naval Air Station Key West " retains its trailing space. That should be soluble by trim().

Datasets like this can be a nightmare. It is worse then the typical 20:80 rule in my experience. 10% of the code gets you 90% of the way but 90% of the code is needed for the other stuff. Vacuously, the punctuation you want to parse by must be present in the right places and absent from all the others, while other variations in spelling and punctuation can still bite hard.

[Eddie Brighton]

Yep.. it is a nightmare indeed! One last thing (while running Andrew Musau 's code...)... I'm pretty satisfied with what I have from your suggested codes. As I'm not familiar with the reshape command, I think I need to somehow attach the new file with the previous data since now I only have the location variables. How is this usually done? I guess I'm having trouble understanding why we need a "keep announcement_locations" command at the beginning.

[Nick Cox]

You don't need that keep statement. I only used it to allow focus on the question. But you need to keep all the data you care about.

[Eddie Brighton]

#7 I see. The problem now without the keep statement is that it's running forever. I hope in the near future STATA includes some kind of progress indicator that helps making decisions whether to move on or wait.

[Nick Cox]

I don't know anything about STATA. More seriously, we all want progress indicators for big jobs in Stata (or anything else) but they can be harder to program than you hope.

You're asking for a data layout that isn't quite the same as the original. In principle, you just need to work on the locations variable and an identifier you can merge back on, but merge itself can be slower than you hope.

A computing book for geographers written some years ago repeatedly urged "now go have a cup of coffee" while something is being done. (Fine by me, but not good advice for anyone who doesn't drink it; the idea generalizes, however: you need to be patient!)

Speed is all relative to expectations. I started out submitting card decks to the one University computer and expecting lineprinter output in a basket a day later. You could tell from the amount of paper what had happened: thin if your program failed, thick if it worked.

[Eddie Brighton]

Yep, I guess you're right about being patient! I'll probably stick around a bit more and see what happens. Thanks.