Hi
I have a messy data which need to be organized. The original file has 500 variables, but for simplicity I came up with a sample excel sheet.
The data has two columns school and contact. The contact information needs to be organized into separate columns (name address phone fax web). The final output should have the following columns
school name address phone fax web
I appreciate your suggestions and help.
School | Contact |
CASTILLEJA SCHOOL | Nancy Ware 1310 Bryant Street Palo Alto California 94301 Phone: (508) 626-1567 Fax: (508) 872-0260 http://www.aaspjhasjearch.net |
CHALLENGER SCHOOL | Wiedmann 1185 Hollenback Avenue Sunnyvale California 94086 Phone:(954) 689-3984 Fax:(954) 689-3985 http://www.akhsksajtech.com |
CENTRAL MIDDLE SCHOOL | Jan Dresser 828 Chestnut Street San Carlos California 94070http://www.aprjaurpimkesolution.com |
Cesar Chavez Academy | 2450 Ralmar Avenue East Palo Alto California 94303 |
CLIFFORD SCHOOL | http://www.cliffodf0galschool.com |
Costano School | Charles Salter |
CRITTENDEN MIDDLE SCHOOL | C. Anzia, Robin Russell, Joyce Murphree and Barbara Saxton phone: (123) 456-7890 |
Hi Thummala,
Since the data is in excel Sheet you can directly import it, using proc import.
In the Second variable has to be devided in to various of iuts sub values as Name, and address, phone etc. but there are no delimiters as mentioned in the sample data. My sugesstions are in steps asd below:
> Using index & substr
#Get the 'Index value' for numbers(0123456789), if its not '1' then there is some value before the door number that would be probably the name. you can substr it by using the length as 'index value' -1.
#Index for 'Phone' & 'Fax' by this you can take the values of address Phone # and Fax # accordingly.
#If there is any othere value remaining in the variable that would be the web page
If the index value goes '0' then the indexing value should be moved to the next indexing value and should be carried on.
This is just a idea and is not fool proof though... you can just try with this as a begining.
Regards,
Moovendhan D
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.