Using the SAS LIBNAME to Process Excel Files - Exchanging Data Between SAS and Microsoft Excel - page 51

However, the MIXED engine connection option has dependencies that are beyond the control of SAS
software. This small feature of the SAS LIBNAME statement is one of the complex concepts dealing with
reading data from Excel into SAS. This complexity comes from the environment that the software has to
deal with. In the introduction to this chapter, I discussed the complexities that relate to SAS, Excel, and
your hardware. Here is where those complexities converge. The following items make implementing this
feature difficult.
1. Hardware (32-bit or 64-bit).
2. Operating System (32-bit or 64-bit).
3. SAS Version (8.2, 9.0, 9.1, 9.2, 9.3, 9.4 - 32-bit or 64-bit).
4. SAS/ACCESS Interface to PC Files Server - for SAS 9.2, 9.3, 9.4, 9.4.1.
5. Microsoft Excel Version: Excel 2003 and Excel 2007 are 32-bit.
6. Microsoft Excel Version: Excel 2010 and Excel 2013 can be either 32-bit or 64-bit.
7. Microsoft JET engine processes file formats prior to Excel 2007.
8. Microsoft ACE engine processes all Excel formats including Excel 2007 and later.
9. Microsoft JET and ACE engines are embedded in the OS and use Windows Registry Keys as
inputs.
10. Changing Microsoft Registry Keys is risky and affects all programs that use the JET/ACE engines.
11. By default, the MIXED engine connection option examines the first eight rows of a spreadsheet.
12. The SAS GUESSINGROWS option is available only to PROC IMPORT.
13. Registry Values TypeGuessRows and ImportMixedTypes have different locations in each OS.
14. None of these things stays the same very long.
15. When using the Excel engine, HEADER=YES and MIXED=YES work.
16. If you are using the PC Files engine, neither of these will work.
Items 1 to 13 listed above combine to make it difficult for each configuration to work exactly the same. The
object of this MIXED option is to convert columns with a mix of numeric and character data into character
data so that a SAS programmer can examine the data within a program and make the adjustments relative
to the expected data and the data read from the file.
The MIXED option works, but by default checks only the first 8 rows of an Excel worksheet (without
registry key changes). The first row is typically considered to be a header row and, if all numeric values are
returned in rows 2 through 7, then the variable is considered to be numeric. In this case, any character
values are returned as missing values. All the worksheet columns are tested, and SAS variables are all
created based upon the results of the JET/ACE engine tests. Also, if the first different row is beyond the
limit of the guessing row parameter, then data of the opposite type is returned as missing. When this
MIXED option is used, then the Excel file is read in import mode, and no updates are allowed to the file.
When the MIXED option has a value of NO (the default value), a specified number of rows of the Excel
column are searched. A guess is returned about the data type of the input Excel data field. The default is to
search the first eight rows of an Excel spreadsheet. Examples later in this chapter will show you how to use
this option and some alternative methods.
NOTE: This option may cause the wrong variable length to be assigned to the field. The Windows Registry
settings TypeGuessRows and ImportMixedTypes control the behavior of this option, and require
administrative privileges to modify. I do not recommend changing these settings. Improperly edited
changes to the Windows Registry can cause your computer to stop working. These settings are described in
the SAS documentation.
4.5.3 PATH Option to Define Physical File Locations
The LIBNAME statement will at times allow you to enter a “<physical-file-name>”. You can
enter this value on the LIBNAME statement when the file type can be determined by the file name.
However, when your computer, operating system, and application software are not all the same bit
configuration, it is more difficult to determine how to access an Excel file using the SAS LIBNAME
statement. Therefore, you need to give SAS some more information about your environment or files that