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