Help using Base SAS procedures

importing excel-multiple variables in one cell

Reply
Occasional Contributor
Posts: 13

importing excel-multiple variables in one cell

How do I import an excel file that has multiple variables in one cell? For instance
1 1001 1 M Day 2 3/16/2011 >276 1.3 >1655 0.17

The item in bold italic is in one cell. How do I separate it into multiple variables. The variables are id,day,sex,phase,date,a1,a2,a3,a4

It has to look like
obs id day sex phase date a1 a2 a3 a4
1 1001 1 M Day 2 3/16/2011 >276 1.3 >1655 0.17

Also the observations in the excel start from 16th or 17th row. The first few rows has other info that are junk. How do I read in data from here and get rid of the junk above.

Thanks a lot for your help.
Valued Guide
Posts: 2,108

Re: importing excel-multiple variables in one cell

Generically, you can import the data as a text string in one variable in SAS. If PROC IMPORT won't skip the early rows, EGuide will. Then you can use the various character functions to parse it out (INDEX..., and SUBSTR are likely tools you will need).
Valued Guide
Posts: 2,168

Re: importing excel-multiple variables in one cell

If you can save the sheet as a CSV file, you'll find the INFILE and INPUT statements will allow you to parse the embedded columns.
Ask a Question
Discussion stats
  • 2 replies
  • 150 views
  • 0 likes
  • 3 in conversation