SAS needs to fix Excel import for fields greater than 255 characters

February 11, 2010
By

This post was kindly contributed by Jared Prins' Blog - SAS - go there to comment and to read the full post.

I have a spreadsheet where one column has character lengths ranging from 0 to 1000 or even more. Importing them has never been much of a problem with this code:

PROC IMPORT DATAFILE=”F:\path\to\file.xls”
OUT=work.a
DBMS=excel2002 REPLACE;
SHEET=”Sheet1″;
GETNAMES=yes;     * variable names are in row 1 ;
RANGE=”Comments$A1:D2870″; * omit if you want to read the entire worksheet;
MIXED=YES; * V. 9: converts numerical values mixed in with character data into a variable with all character values;
SCANTEXT=yes;   * V. 9 only;
USEDATE=no;    * V. 9 only – read with date format only (note date-time);
SCANTIME=no;    * V. 9 only;
DBSASLABEL= none; * V. 9 only;
RUN;

I ran this code on my new dataset, and the column was truncated to 255 characters.  The SCANTEXT option above apparently only scans the first 8 rows to determine the field length.  I tried a 255 limit hack listed on the SAS Google Group.  The hack involves adding a dummy row at the top with the character length you need. It will automatically format your column to an appropriate size.

Frustratingly, it did not work.  Even using the other option textsize did not work:

textsize=30000;  * valid values are 1 to 32767;

My workaround involved importing the data to an Access Database and then importing that into SAS.  Interestingly, the textsize option works great when importing from Access.

I’ve never had issues before, so maybe this problem is only introduced with SAS 9.2 TS2M2?  Or maybe an odd character in my data is throwing things off?  (The % sign in my text data seemed to bugger up importing into Access. Maybe something similar is happening for SAS?)

Like duct tape is to a car bumper, hacks like these are to data.  It only works for the short term.

This post was kindly contributed by Jared Prins' Blog - SAS - go there to comment and to read the full post.

Tags: ,

Proc-x is looking for sponsors!

Dear readers, proc-x is looking for sponsors who would be willing to support the site in exchange for banner ads in the right sidebar of the site. If you are interested, please e-mail me at: [email protected]

Welcome!

SAS-X.com offers news and tutorials about the various SAS® software packages, contributed by bloggers. You are welcome to subscribe to e-mail updates, or add your SAS-blog to the site.

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.