DATA Step, Macro, Functions and more

Separate City and State

Reply
Super Contributor
Posts: 647

Separate City and State

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,

Respected Advisor
Posts: 3,777

Re: Separate City and State

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

Super Contributor
Posts: 647

Re: Separate City and State

Yes

Respected Advisor
Posts: 3,777

Re: Separate City and State

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?

Respected Advisor
Posts: 3,777

Re: Separate City and State

Another hint.  RTM about the CALL version of SCAN.

Super User
Posts: 5,072

Re: Separate City and State

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.

Respected Advisor
Posts: 3,777

Re: Separate City and State

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;
Super Contributor
Posts: 647

Re: Separate City and State

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

PROC Star
Posts: 7,356

Re: Separate City and State

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.

Super Contributor
Posts: 647

Re: Separate City and State

Thats true.

Respected Advisor
Posts: 3,777

Re: Separate City and State

show your work

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Separate City and State

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


j


state


city


Montgomery AL


Montgomery AL *


15


AL


Montgomery


Juneau AK


Juneau AK *


11


AK


Juneau


Phoenix AZ


Phoenix AZ *


12


AZ


Phoenix


Little Rock AR


Little Rock AR *


16


AR


Little Rock


Sacramento CA


Sacramento CA *


15


CA


Sacramento


Denver CO


Denver CO *


11


CO


Denver


Hartford CT


Hartford CT *


13


CT


Hartford


Dover DE


Dover DE *


10


DE


Dover


Tallahassee FL


Tallahassee FL *


16


FL


Tallahassee

Regular Contributor
Posts: 195

Re: Separate City and State

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

Ask a Question
Discussion stats
  • 12 replies
  • 402 views
  • 0 likes
  • 6 in conversation