If a column in Excel has both mixed (numeric and character) data and
character data with a shorter length (less than 255 bytes) at the
beginning of the column and longer length (more than 255 bytes) further
down in the column, the character data may be truncated when both
MIXED=YES and SCANTEXT=YES are specified, even if the value of
TypeGuessRows is set to 0.

When you import an Excel spreadsheet into SAS, the MS Jet engine relies
on a windows registry key called TypeGuessRows to know how many rows of
the file to scan in order to determine variable attributes such as type
and length. The official range set by Microsoft is 0-16 and the default
value is 8.  If you have mixed data within the column, the data may be
imported as missing or as a different type than is expected. If you have
text data with different lengths, the text could be truncated.  However,
if the value is set to 0, then up to 16384 rows in the spreadsheet are
checked.

In SAS 9.1, setting the value of TypeGuessRows to 0, along with
specifying MIXED=YES and SCANTEXT=YES in PROC IMPORT, should import the
data with the correct type and length.  However, due to a bug in the MS
Jet Provider, values in an Excel column with mixed data and long text
strings may be truncated when imported into SAS.  If MIXED=YES and
SCANTEXT=YES, the character variable will have all the data imported,
but a length of 255 bytes.  If MIXED=NO and SCANTEXT=YES, then the data
will have the correct length but some data may be missing.

As a workaround, you can use the LIBNAME access method with the
MIXED=YES option, and the DBSASTYPE data set option:

   libname db "C:\excel files\test1.xls"  mixed=yes;

   data test3;
     set db."Sheet1$"n(DBSASTYPE=(var='char(1024)'));
   run;

   libname db;

Operating System and Release Information