topic Re: importing excel-multiple variables in one cell in SAS Procedures

importing excel-multiple variables in one cell

saslover — Tue, 07 Jun 2011 18:44:29 GMT

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.

Re: importing excel-multiple variables in one cell

Doc_Duke — Tue, 07 Jun 2011 20:52:17 GMT

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

Re: importing excel-multiple variables in one cell

Peter_C — Thu, 09 Jun 2011 07:19:13 GMT

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.