• Print |
• E-mail  |
• Bookmark

When using SAS/ACCESS to PCFF software to import an Excel 97 or above
file with Proc Import or Import Wizard, column data may be imported
as missing. This can happen if you have mixed data within the column.
The SAS API will determine the appropriate data type based on the most
common data type it finds in the first 8 rows. There are a couple of
things that can be done:

1. If you want the data to be read in as character you can do the
   following to 'reformat' the column as character:

     In Excel, highlight the column in question. Select Data-Text to
     Columns. Select NEXT until Step 3 of 3. Under 'Column data format'
     select Text. Finally, select Finish. The column should now be
     left justified.

     While the column is still highlighted, select FORMAT-CELLS-TEXT.

2. If you want the data to be read in as numeric you can do the
   following:

     In Excel, you will have to make sure that you have only numbers
     in each cell. This also includes removing any blanks since they
     are treated as a character. You can remove a blank by simply
     hitting 'delete' in the cell. Now, highlight the column and select
     Format-Cells. Select 'Number'. Your column should now be right
     justified.

3. Save the Excel file as Excel 5/95 and use Proc Access. Proc Access
   has statements, Mixed and Type, that support reading mixed data.
   See SASNote 7059 for syntax.

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:

   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
         16384. This may cause a small performance hit if the file
         is large.

5. Upgrade to SAS V9.0 and use the Access libname engine to Excel with
   the mixed= libname option:

   libname mylib excel 'c:\myexcel.xls' mixed=yes;


6. Beginning in SAS 9.1, the Import Procedure supports the
   optional statement MIXED=YES.


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 when
you edit the Windows registry: changes in the Windows registry
can render your system unusable and will require that you reinstall
the operating system.

Operating System and Release Information