BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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,

12 REPLIES 12
data_null__
Jade | Level 19

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

SASPhile
Quartz | Level 8

Yes

data_null__
Jade | Level 19

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?

data_null__
Jade | Level 19

Another hint.  RTM about the CALL version of SCAN.

Astounding
PROC Star

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.

data_null__
Jade | Level 19

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;
SASPhile
Quartz | Level 8

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

art297
Opal | Level 21

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.

SASPhile
Quartz | Level 8

Thats true.

data_null__
Jade | Level 19

show your work

Mit
Calcite | Level 5 Mit
Calcite | Level 5

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

UrvishShah
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2179 views
  • 0 likes
  • 6 in conversation