IIRC, there is some rule SAS uses to decide whether a column in Excel is
character or numeric. By default it checks only the first 8 rows, and
makes a decision based on whether there is more alpha data or numeric
data. I seem to recall there being a way to expand this option so it
would check all rows, maybe by messing with the register?
I found the following link:
which describes the issue, and says things may get better in v9/9.1:
Using SAS 9.0, customers can use Excel LIBNAME statement with the
Beginning with SAS 9.1, the IMPORT procedure will support the option
This option will allow automatic conversion of numeric to character when
the datatype has been determined to be character.
That sounds a little better, but not quite what I would prefer. My memory
is DBMS copy simply made the rule "If the column has any non-numeric data,
make it a character variable and convert numerics to character." I liked
that method, and with SAS's purchase of DBMS copy, was hopeful that some
of Feldman's conversion routines would get incorporated into proc import,
On Wed, 3 Mar 2004 07:36:43 -0800, Choate, Paul@DDS <pchoate@DDS.CA.GOV>
>Dear SS - A favorite kludge...insert some dummy alpha data in the first
>rows, import, and then delete those rows.
>DDS Data Extraction
>From: S. S. [mailto:sassysaser@YAHOO.COM]
>Sent: Wednesday, March 03, 2004 7:14 AM
>Subject: Read Excel into SAS
>I have an Excel file with a column. Most of the values for this column
>are numbers, and the rest are characters. In Excel, I formatted the
>column to be a text field. Then in SAS I referred to this file with
>the following codes:
>libname excel odbc complete="dsn=Excel Files;dbq=C:\Master.xls";
>The problem is the column is set by SAS as a numeric field and the
>cells where the original values are characters are now missing. What
>should I do?