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

[talisman]

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

[PeterClemmensen · Accepted solution]

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;

[talisman]

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 

[Astounding]

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.

[Kurt_Bremser]

Please do not double-post.

[talisman]

Sorry about that.