Microsoft Excel Workbook Files

LIBNAME Statement

By default, the SAS LIBNAME statement connects to a Microsoft Excel file in limited READ/WRITE mode. Although you can read data, delete a table, or create a new table, you cannot update data or append a new data row. To allow data update and append, set the LIBNAME option SCANTEXT=NO.
Because the SAS PROC SQL pass-through connects to a Microsoft Excel file in READ/WRITE mode. You can read, write, and update data by passing SQL command statements.
CAUTION:
Due to the use of the Microsoft Jet Excel engine and the Microsoft Ace Excel engine, the SAS engines for Excel have limited update and delete capability. There might be other unsolved issues. You should therefore avoid using the update and delete features. Back up your Excel files before you try using any update functions.
CAUTION:
Although you can connect to an Excel file while the application is opening it, any updates to the Excel file can cause the SAS Excel engine to malfunction. It is recommended that you close the application that is using the Excel file, disconnect, and then reconnect the file in SAS. Set the LIBNAME option, FILELOCK=YES, to ensure that Excel or other applications have not opened the connected Excel file.

Connection Options

You can use this connection option in the LIBNAME statement or in the PROC SQL CONNECT statement to connect to a Microsoft Excel file.
HEADER= YES | NO
determines whether the first row of data in a Microsoft Excel range (or spreadsheet) are column names.
YES
specifies to use the first row of data in an Excel range (or spreadsheet) as column names.
NO
specifies not to use the first row of data as column names in an Excel range (or spreadsheet). SAS generates and uses the variable names F1, F2, F3, and so on.
Alias:HDR | GETNAMES
Default:YES
Note:This connection option is only for reading Microsoft Excel spreadsheets. This option is ignored when you are writing data to an Excel spreadsheet.
MIXED= YES | NO
specifies whether to convert numeric data values into character data values for a column of mixed data types. This option is valid only when importing (reading) data from Excel. The Microsoft Ace and Jet Excel engines handle this option.
YES
assigns a SAS character type for the column and converts all numeric data values to character data values when mixed data types are found. When you specify MIXED=YES, the connection is set in import mode and no updates are allowed.
CAUTION:
Due to a limitation in the Microsoft Ace and Microsoft Jet engines, MIXED=YES could result in improper text variable lengths.
NO
assigns numeric or character type for the column, depending on the majority of the type data that is found. Both numeric data in a character column and character data in a numeric column are imported as missing values.
Default: NO
Restrictions: DBMS= only

This option is available only for Windows reading Excel data into SAS. You cannot use this option for delimited files.

Registry settings might affect the behavior of the MIXED= option. Refer to the following tables for Registry Key location and Registry Settings for the MIXED= option for additional information.

Registry Key for TypeGuessRow Based on Office Version or Engine
Environment
Office Version or Engine
Registry Key
Windows
Office 2007
HKEY_LOCAL_MACHINE then selectSoftware then selectMicrosoft then selectOffice then select12.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows running
9.2 TS2M0 or later
Office 2010
HKEY_LOCAL_MACHINE then selectSoftware then selectMicrosoft then selectOffice then select14.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows 7 or
X64 system
Office 2007
HKEY_LOCAL_MACHINE then selectSoftware then selectWow6432Nodethen selectMicrosoft then selectOffice then select12.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows 7 or
X64 system
Office 2010
32-bit
HKEY_LOCAL_MACHINE then selectSoftware then selectWow6432Nodethen selectMicrosoft then selectOffice then select14.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows 7 or
X64 system
Office 2010
64-bit
HKEY_LOCAL_MACHINE then selectSoftware then selectMicrosoft then selectOffice then select14.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows
ACE Engine
HKEY_LOCAL_MACHINE then selectSoftware then selectMicrosoft then selectOffice then select12.0 then selectAccess Connectivity Engine then selectEnginesthen selectExcel
Windows
Jet Engine
(when using MSENGINE=JET only)
HKEY_LOCAL_MACHINE then selectSoftware then selectMicrosoft then selectJet then select4.0 then selectEnginesthen selectExcel
Registry Settings for the MIXED Option
TypeGuessRows
An integer type with a default value of 8. You can use the number of rows in the worksheet range in scans to determine column types. If you set this type to 0, all rows in the range are checked. Microsoft states that the valid range of TypeGuessRows is 0–16. However, you could set as high as 16384 and it would still operate correctly.
CAUTION
Changing the TypeGuessRows value cause a scan to fail if you set it the value higher than 16384. Changes also affect any software that uses the Microsoft Jet provider to access Excel file data, including accessing Excel data in a Microsoft Access database. The TypeGuessRows value is registered with and controlled by Microsoft. It is therefore recommended that you set the value to 0
ImportMixedTypes
A string type with a default value of Text. While scanning TypeGuessRows rows, if a column has multiple data types, the column type is Text. This is only if the value of the setting is Text. If the value of the setting is Majority, the most common data type in the column is set as the column type.
For the MIXED=YES option to work correctly, you should you change TypeGuessingRows to 0 in the Microsoft Windows registry so that all rows in the specified range are scanned. As a result, when you use MIXED=YES, the Jet provider always assigns character type for columns with data of mixed data types and converts numeric data to character data.
TypeGuessRows
an integer type with a default value of 8. The number of rows in the worksheet range is used to scan and determine column types. If set to 0, then all rows in the range are checked. Microsoft states that the valid range of TypeGuessRows is 0 to 16. However, it can be set as high as 16,384 and still operate correctly.
ImportMixedTypes
a string type with a default value of Text. If a column contains more than one type of data (during the scan of TypeGuessRows rows), the column type is Text if the value is Text. If the value is Majority, the most common type determines the column type.
CAUTION:
These settings are registered by the Microsoft Jet engine. Changing settings such as TypeGuessRows for Microsoft Jet engine in the Windows registry affects all software that uses Microsoft Jet engine to import data. This includes Microsoft Office products, as well as other database products and application software.
Note: For the MIXED=YES option to work correctly, change TypeGuessingRows to 0 in the Windows registry. All rows in the specified range are scanned. Now when you use MIXED=YES, the Jet provider always assigns character type for columns with mixed data types. It also converts numeric data to character data.
VERSION= '2007' |'2003'|'2002'|'2000'|'97'|'95'|'5'
specifies the version of the file that you want to create if the file does not exist. Valid values are 2003, 2002, 2000, 97, 95, and 5. The default value is 97.
Alias:VER
Notes:You do not need to specify this option if you do not know the version of your Microsoft Excel file. However, if you want to create a new Microsoft Excel file, you can use this option to specify the version that you create. There is no need to specify the VERSION= value if you want to create a 2007 Excel .xlsb or .xlsx file. The file extension tells SAS the VERSION= value.

Versions 2003, 2002, 2000, and 97 are treated as the same format. Versions 95 and 5 share the same format.

Data Types Conversion

This table shows the default SAS variable formats that SAS/ACCESS assigns to Excel data types. The data formats are assigned when SAS reads data from Microsoft Excel spreadsheets using the LIBNAME engine.
Default SAS Formats Assigned for Excel Formats
Excel Column
Format
SAS Variable
Format
SAS Variable
Type
Text
$w.
Character
General
Numeric
Number
Numeric
Scientific
Numeric
Percentage
See Note 3
Numeric
Fraction
See Note 3
Numeric
Currency
DOLLAR21.2
Numeric
Accounting
DOLLAR21.2
Numeric
Date
DATE9. See Note 12
Numeric
Time
TIME8.12
Numeric
1The default format is DATE9. You can use USEDATE=NO to change format from DATE9. to DATETIME. You can also use the SASDATEFMT= option to change the format of other SAS date and time formats.
2Note that the SAS date time value uses 01Jan1960 as a cutoff line, while the Jet provider date value uses 30Dec1899 as a cutoff line for internal values.
3To access Fraction or Percent format data in your Excel file, you can use the FORMAT statement to assign the FRACT. or PERCENT. format in your DATA step code.
The following table shows the default Excel data types that SAS/ACCESS assigns to SAS variable formats. These data types are assigned when SAS writes data to an Excel file using the LIBNAME engine. You can override these default conversions by using DBTYPE during output processing.
Default Excel Formats Assigned for SAS Variable Formats
SAS Variable
Format
XLS Column
Data Type
$BINARYw.
Text
$CHARw.
Text
$HEX w.
Text
$w.
Text
w.d
Number
BESTw.
Number
BINARYw.
Number
COMMA w.d
Number
COMMAXw.d
Number
Ew.
Number
FRACTw.
Number
HEXw.
Number
NEGPARENw.d
Number
PERCENTw.d
Number
DOLLARw.d
Currency
DOLLARXw.d
Currency
DATEw.
Date
DATETIMEw.d
Date
DDMMYYw.
Date
HHMMw.d
Time
JULDAYw.
Date
JULIANw.
Date
MMDDYYw.
Date
MMYYw.d
Date
MONTHw.
Date
MOYYw.
Date
WEEKDATEw.
Date
WEEKDATXw.
Date
WEEKDAYw.
Date
WORDDATEw.
Date
WORDDATXw.
Date

Processing Date and Time Values between SAS and Microsoft Excel

To import date or time values from a Microsoft Excel file, the SAS LIBNAME engine reads date values using DATE9. by default. Time values are assigned the TIME8. format. However, you can set the LIBNAME option, USEDATE=NO, or the LIBNAME statement, USEDATE=NO. Using the IMPORT procedure, you can have date and time values read in using the DATETIME. format.
To export SAS values with DATE, TIME, or DATETIME formats to a Microsoft Excel file, values are written using DATE9. format. When you see values with the date 1/0/1900 in Microsoft Excel, format them using the TIME format to display the correct time values.
TIME_VAL: By default, the SAS/ACCESS LIBNAME engine loads the SAS time value in the ACCESS database. The time value is set to less than or equal to 24 hours. Set the time environment variables follows: 
OPTIONS  SET=TIME_VAL SAS:
To load SAS time values with the SAS date and time base of 01Jan1960:00:00:00, enable the time value to be reserved for longer than one day. When you import data with SCANTIME=YES, SAS scans date and time values in the column and assigns the TIME8. format if all the values in the column are in the year 1960. If this is not the case, SAS assigns the DATETIME format for the column. You can reset the TIME_VAL back to the default value with the following code: 
OPTIONS SET=TIME_VAL_ONEDAY;
Copyright © SAS Institute Inc. All rights reserved.