Splitting a String

[DFlynn24]

Hey everyone,

I have a bit of a problem extracting certain words from a string to make new variables.  I know that the scan function can normally be used to solve this problem quite easily, but the problem I have is that some of the entries for the new variable contain more that one word.

For example (using an address):  123 Main Street, Los Angeles, California, 11000

                                                      123 North Main Street, Los Angeles, California, 11000.

As is stands right now, those addresses are lumped into one variable.  I want to extract the strings so I have a distinct variable for: Street, City, State, and Zip Code.  The problem with using a basic SCAN function is that streets with two-word names are not captured correctly.  Does anyone have a solution??

[Reeza · Accepted solution]

Look at the parameters in SCAN() function.

Specifically specifying the delimiter. If it's as in your example, then use the comma as your delimiter in the SCAN function.

The 'basic' scan function will work 🙂

[ad123123]

Hi,

Assuming that strings are given in the same fashion as mentioned in your problem statement,  following code will help:-

data have;
length add $ 1000;
add='123 Main Street, Los Angeles, California, 11000';
output;
add='123 North Main Street, Los Angeles, California, 11000';
output;
run;

data want;
set have;
house_no=substr(add,1,3);
street=substr(strip(scan(add,1,',')),4);
city=strip(scan(add,2,','));
state=strip(scan(add,3,','));
zipcode=strip(scan(add,4,','));
run;

Please let me know if it helps.

Regards,

Abd.

[DFlynn24]

Reeza,

You were certainly correct, forgot that I could specify a comma delimiter in the SCAN function.  Thanks for your help!