topic Re: Separate City and State in SAS Programming

Separate City and State

SASPhile — Wed, 06 Nov 2013 18:15:16 GMT

There is a column that has city and state together. How to separate them?I used scan function.But there are many ways in which the data is stored as shown below:

EAST TEMPLETO MA

ST. PETE FL

One thing that is definete is , scan(variable_name,-1,'') is giving me state, but the issue is with City,

Re: Separate City and State

data_null__ — Wed, 06 Nov 2013 18:20:14 GMT

Can you assume that everything except the last word (state) is the city?

Re: Separate City and State

SASPhile — Wed, 06 Nov 2013 18:22:31 GMT

Yes

Re: Separate City and State

data_null__ — Wed, 06 Nov 2013 18:28:55 GMT

Then all you need to know if the column number of the character (blank) that is just before the last word. Maybe ANYBLANK with negative start. Is any blank a function?

Re: Separate City and State

data_null__ — Wed, 06 Nov 2013 18:30:22 GMT

Another hint. RTM about the CALL version of SCAN.

Re: Separate City and State

Astounding — Wed, 06 Nov 2013 19:23:09 GMT

You're saying you can extract STATE. Then it should be easy enough to get CITY:

city = substr(original_variable, 1, length(original_variable) - length(state));

Also note that this code gives CITY a length of $200. You may want to set a shorter length before adding this statement. If the length of CITY might vary from data set to data set, you could always try this first:

city = original_variable;

Any advice from data _null_ that inclues RTFM is best heeded. A more complex problem would require better tools than SUBSTR.

Good luck.

Re: Separate City and State

data_null__ — Wed, 06 Nov 2013 19:43:09 GMT

I was hopping that would RTM and come back with this...

data city;
   input addline3 $40.; 
   call scan(addline3,-1,s,l);
   length city $40 state $2; 
   city = substr(addline3,1,s-1);
   state = substr(addline3,s);
   cards; 
EAST TEMPLETO      MA
ST. PETE   FL
;;;;
   run; 
proc print; 
   run;

Re: Separate City and State

SASPhile — Wed, 06 Nov 2013 22:29:21 GMT

when I run this, i get an error invalid thrid argument for substr

Re: Separate City and State

Mit — Thu, 07 Nov 2013 01:27:08 GMT

Assuming the states are always two character the following codes would help(tested correct).

Otherwise look for the position of the spaces.

data have1;

set have;

city_state1=compbl(city_state||'*');

run;

data want;

set have1;

j=index(city_state1,'*');

state=substr(city_state1,j-3,j-2);

city=substr(city_state1,1,j-4);

state=tranwrd(state,'*','');

drop city_state1 j;

run;

city_state

city_state1

state

city

Montgomery AL

Montgomery AL *

Montgomery

Juneau AK

Juneau AK *

Juneau

Phoenix AZ

Phoenix AZ *

Phoenix

Little Rock AR

Little Rock AR *

Little Rock

Sacramento CA

Sacramento CA *

Sacramento

Denver CO

Denver CO *

Denver

Hartford CT

Hartford CT *

Hartford

Dover DE

Dover DE *

Dover

Tallahassee FL

Tallahassee FL *

Tallahassee

Re: Separate City and State

art297 — Thu, 07 Nov 2013 01:51:09 GMT

Can you provide an example where you get such an error using data_null_'s suggested code? I can only imagine that happening if you have a blank line, or only a city or state, but you've already stated that state will always be the last text.

Re: Separate City and State

SASPhile — Thu, 07 Nov 2013 04:05:52 GMT

Thats true.

Re: Separate City and State

data_null__ — Thu, 07 Nov 2013 11:30:26 GMT

show your work

Re: Separate City and State

UrvishShah — Thu, 07 Nov 2013 13:35:49 GMT

Hi,

Here is my try...Might be it will same as others' logic...

data have(drop = position length);

input test_var $50.;

length state $5. city $20.;

state = scan(test_var,-1," ");

call scan(test_var,-1,position,length);

city = substr(test_var,1,(position-1));

cards4;

EAST TEMPLETO MA

ST. PETE FL

;;;;

-Urvish