Help using Base SAS procedures

excel to sas

Reply
Frequent Contributor
Posts: 145

excel to sas

how can convert excel file into sas dataset using infile statement.....I was using proc import but values are getting truncated........my excel has 1st row as variable names and every collumn in excel corresponds to variable name...can someone help infile statement for this kind of excel....

Trusted Advisor
Posts: 2,116

Re: excel to sas

Posted in reply to rakeshvvv

Reading Excel via a DATA step is painful and Excel version dependent.  You might have a chance with .xlsx files as they are stored as .xml files.

When I have that problem, there are a couple of approaches to take.

-- look at the SCANTEXT and TEXTSIZE options in PROC IMPORT.  They may need to be changed to control truncation.

-- use SAS Enterprise Guide to read the data.  I fined it a bit more flexible than IMPORT for messy data.

Respected Advisor
Posts: 4,935

Re: excel to sas

Posted in reply to rakeshvvv

LIBNAME gives you the most flexibility. Yet, in Excel data type is defined at the cell level and you are trying to import the data into a (database) structure where data type is defined on a column basis.

Sometimes, when possible, the simplest way to successful importation is to modify the Excel sheet. Sometimes, you must read cell values as character and accept the incurring loss of precision. No solution is optimal in all cases.

Please post an example of your data to get more specific advice.

PG

PG
Ask a Question
Discussion stats
  • 2 replies
  • 274 views
  • 1 like
  • 3 in conversation