topic Re: How to separate one column with multi name cities, state and zipcode into separate columns in New SAS User

How to separate one column with multi name cities, state and zipcode into separate columns

talisman — Mon, 24 Oct 2022 03:11:13 GMT

one column named City_State_Zipcode and the data looks like this in the column

Brooklyn NY 11218

West Haven MN 99556

West Charlotte NC 12563

How do you separate that one column into three separate columns named City, State, Zipcode

How to separate one column with multi name cities, state and zipcode into separate columns

talisman — Mon, 24 Oct 2022 03:58:52 GMT

one column named City_State_Zipcode and the data looks like this in the column

Brooklyn NY 11218

West Haven MN 99556

West Charlotte NC 12563

How do you separate that one column into three separate columns named City, State, Zipcode?

When I try to do it, West will end up in the City column and Haven will be in the State column and MN will be in the zipcode column

Re: How to separate one column with multi name cities, state and zipcode into separate columns

PeterClemmensen — Mon, 24 Oct 2022 04:00:34 GMT

data have;
input City_State_Zipcode $30.;
datalines;
Brooklyn NY 11218       
West Haven MN 99556     
West Charlotte NC 12563 
;

data want(drop = pos length);
   set have;
   call scan(City_State_Zipcode, -2, pos, length);
   city  = substr(City_State_Zipcode, 1, pos-2);
   state = scan(City_State_Zipcode, -2);
   zip   = scan(City_State_Zipcode, -1);
run;

Re: How to separate one column with multi name cities, state and zipcode into separate columns

Astounding — Mon, 24 Oct 2022 04:04:23 GMT

Try it this way:

data want;
   set have;
   zip = scan(city_state_zipcode, -1);
   state = scan(city_state_zipcode, -1);
   city = substr(city_state_zipcode, 1, length(city_state_zipcode) - 8);
run;

The final formula might need to be adjusted if it is possible that some STATE values have a length other than 2, or some zipcodes have a length other than 5. But if this gives the answer as is, let's leave out the complexities.

Re: How to separate one column with multi name cities, state and zipcode into separate columns

Kurt_Bremser — Mon, 24 Oct 2022 04:25:00 GMT

Please do not double-post.

Re: How to separate one column with multi name cities, state and zipcode into separate columns

talisman — Mon, 24 Oct 2022 05:46:37 GMT

Sorry about that.