Help using Base SAS procedures

Parsing

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Parsing

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.

AddressCityStateZip Code
2453 Clairmount StDetroit MI48206

Thanks


Accepted Solutions
Solution
‎02-26-2015 04:27 PM
PROC Star
Posts: 7,363

Re: Parsing

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;

View solution in original post


All Replies
Super User
Posts: 17,840

Re: Parsing

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..

Contributor
Posts: 28

Re: Parsing

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

Solution
‎02-26-2015 04:27 PM
PROC Star
Posts: 7,363

Re: Parsing

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 214 views
  • 1 like
  • 3 in conversation