When you import data directly from Excel into SAS, the API used to read the Excel file determines the data type and other attributes such as length from the first 8 rows of data in the Excel spreadsheet. This could cause your data to be truncated if you have text with a shorter length at the beginning of a column, and text with a longer length further down in the column.

There are a couple of things you can do to get around this issue:

1) You can add 'dummy' data to the first row in the Excel spreadsheet so the length is determined correctly.

2) You can save the Excel data as a comma-delimited text file and read the file with either PROC IMPORT, the Import Wizard, or DATA step. If you use PROC IMPORT or the Import Wizard, the API is not involved with reading the text file, and by default, the first 20 rows of data are scanned to determine the variable attributes. If this is not enough, you can change the value of GUESSINGROWS to an appropriate value.

Reference SAS KB0036190 to see how to change the value of GUESSINGROWS.

3) Save the Excel file as Excel 5/95 and use PROC ACCESS. PROC ACCESS has an option called SCANTYPE that you can use to scan an entire row for attributes. Keep in mind that Excel 5 has a limit of 16,383 rows. The syntax is below:

PROC ACCESS DBMS=XLS;
       CREATE WORK.TEST.ACCESS;
       PATH='C:\my documents\mixed_data.XLS';
       SCANTYPE=YES;    /* Scans entire column to determine variable
                           type */
       GETNAMES=YES;    /* Reads first row for column names */
       ASSIGN=YES;      /* Assigns SAS variables the names read above */
       MIXED=YES;       /* Allows num-to-char conversion in mixed data
                           columns */
       TYPE address=c;  /* This will force the variable 'address' to
                           come in as character */
       CREATE WORK.TEST.VIEW;
       SELECT ALL;
       RUN;
       PROC ACCESS VIEWDESC=WORK.TEST
            OUT=WORK.NEWTEST;   /*Create SAS dataset, work.newtest,
                                  from the view descriptor, work.test */
       RUN;

4. As previously stated, the API used to read the Excel file determines the data type from the first 8 rows. The number of rows evaluated can be changed by altering a value in the Windows regsitry. This can be done as follows:

Warning: Always back up your registry before you make any registry changes. For assistance, see Windows Help, Microsoft documentation, or the Microsoft Windows Web site. SAS is not responsible for user error when editing the Windows registry: Incorrect changes in the Windows registry can render your system unusable and will require that the operating system be reinstalled.

On the Start menu, click Run. In the Run dialog type 'regedit' and click OK. Open the following Key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Double click TypeGuessRows. In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive for Value data. Click OK and exit Regedit.

NOTE: Valid ranges of values for TypeGuessRows key is 0 to 16. However, if the value is 0 the number of rows scanned is 16383. This may cause a small performance hit if the file is large.

In SAS 9.1, there is a new option in PROC IMPORT you can use called SCANTEXT, which scans all rows of data for the largest size. Please note that SCANTEXT works in conjunction with TypeGuessRows, so it will scan as many rows as is defined in TypeGuessRows to determine the appropriate length. Depending on your data, you may have to use SCANTEXT and also increase the value of TypeGuessRows to get the correct length.

PROC IMPORT  OUT= work.invoice
            DATAFILE= "c:\excel\sasdemo.xls"
            DBMS=EXCEL REPLACE ;
          SHEET="Invoice$";     /* Sheet name */
          GETNAMES=YES;
          SCANTEXT=YES;
RUN;