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
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;
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..
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.