Parsing

[gobejo]

Hi

How would i parse this data: "2453 Clairmount St, Detroit, MI, 48206" and create 3 new variables Address, City, State and Zip code. The number of character in address and state is not consistent. I have to do this for a few hundred thousand rows.

Address	City	State	Zip Code
2453 Clairmount St	Detroit	MI	48206

Thanks

[Reeza]

If the comma delimiters is consistent throughout the variables you can look into the scan function:

address=scan(variable, 1, ",");

city=scan(variable, 2, ",");

etc..

[gobejo]

Reeza,

I did not communicate clearly. Sorry about that.

i am looking to create a new dataset from an existing data set which has 14 variables and 252 rows.

The existing data set has Address = 2453 Clairmount St, Detroit, MI, 48206

I want the new dataset to have 18 rvariables (in which 14 are existing and 4 are created) and 252 rows.

This is what i have written so far

Data work.report2_test;

SET work.REPORT1_TEST

Address=scan('Address',1, ",");

City=scan('Address',2, ",");

State=scan('Address',3, ",");

Zipcode=scan('Address',4, ",");

RUN;

but the out put i get is wrong.

Thanks

[art297 · Accepted solution]

There are problems with your code. You are missing a semi-colon on your set statement. You are re-writing the address field with your first scan statement, and the address variable shouldn't be in quotes. Try:

Data work.report2_test;

SET work.REPORT1_TEST;

street=scan(Address,1, ",");

City=scan(Address,2, ",");

State=scan(Address,3, ",");

Zipcode=scan(Address,4, ",");

RUN;