Microsoft Excel Workbook Files

Microsoft Excel Files Essentials

SAS/ACCESS Interface to PC Files Server works with Microsoft Excel workbook 5, 95, 97, 2000, 2002, 2003, 2007, and 2010. These files are referred to collectively in this document as XLS, XLSB, XLSM, or XLSX files.
An Excel file represents an Excel workbook. An Excel workbook is a collection of worksheets.
A cell is the intersection of a column and a row. It is referenced by a column number and a row number. For example, B5. A cell is the basic unit that saves data in the worksheet. A cell can contain a numeric value or a text value of up to 32,767 characters.
A range is a subset of cells in a worksheet. Its address identifies it. It begins with the name of the top left cell and ends with the name of the bottom right cell, separated by two periods. For example, the range B2..E8 is the range address for a rectangular block of 12 cells, where the top left cell is B2 and the bottom right cell is E8 (shown as shaded, below). A range name identifies a range.
A worksheet in a .xls file can save up to 256 columns and 65,536 rows. Excel 2007 has been enhanced to support 16,384 columns and 1,048,576 rows in a worksheet. Files that are created with Excel 2007 can have an .xlsb, .xlsx, or .xlsm extension. Due to the limitations of the ACE engine and the Jet engine, the SAS LIBNAME engine is limited to 255 columns in an Excel file.
A Range of Data in an Excel Worksheet
Range of Data in an Excel Worksheet
SAS/ACCESS Interface to PC Files Server treats an Excel workbook as a database, and a range (subset of cells in a worksheet) as a table. A range name must be defined in the Excel file before SAS can use it. A worksheet is treated as a special range. A worksheet name appended with a $ character is treated as a range.
For example, Sheet1 is a sheet name in an Excel file. SAS treats Sheet1$ as a valid range name and uses it to refer to the whole worksheet. You need to use a SAS name literal when referring to the sheet name. For example, 'Sheet1$'n. The first row of data in a range is usually treated as a column heading and used to create a SAS variables name.
Remember the following points as you work with Microsoft Excel files.
  • Excel 4 files allow only one spreadsheet per file.
  • Excel 4, Excel 5, and Excel 95 limits are 256 columns and 16,384 rows.
  • Excel 97, 2000, 2002, 2003 limits are 256 columns and 65,536 rows.
  • Excel 2007 limits are 16,384 columns and 1,048,576 rows.
  • Excel 95 files are treated as the same format as Excel 5 files.
  • Excel 2000, 2002, and 2003 files with an .xls file extension are treated as the same format as Excel 97 files.
  • Excel 2007 and 2010 have three different file extensions:
    • .xlsb
    • .xlsm
    • .xlsx

Excel Data Types

Microsoft Excel software has two data types: character and numeric.
  • Character data can be labels or formula strings. Character data is generally considered text and can include character type dates and numbers. A cell can save up to 32,767 characters.
  • Numeric data can be numbers, formulas, or error values. Numeric data can include numbers (0 through 9), formulas, or error values (such as #NULL!, #N/A, #VALUE!).

Excel Numeric Data and Time Values

Numeric data can also include date and time values. The conversion of date and time values between SAS data sets and Microsoft Excel spreadsheets is transparent to users. However, you are encouraged to understand the differences between them.
In Microsoft Excel software, a date value is the integer portion of a number that can range from 01 January 1900 (saved as integer value: 1) to 31 December 9999 (saved as integer value: 2,958,465). A Microsoft Excel software time value is the decimal portion of a number that represents time as a proportion of a day. For example, 0.0 is midnight, 0.5 is noon, and 0.999988 is 23:59:59 (on a 24-hour clock). While a number can have both a date and a time portion, the formats in Microsoft Excel display a number in a date, time, or date and time format.
In SAS software, SAS dates are valid back to AD 1582 and ahead to AD 9999. A date value is represented by the number of days between January 01, 1960, and that date. A time value is represented by the number of seconds between midnight and that time of day. A datetime value is represented by the number of seconds between midnight January 01, 1960, and that datetime.
When you export a SAS time value to an Excel file, the value could be displayed as “1/0/1900” in the Excel file. Format the cell with a Time format to see the time value displayed correctly.

Excel File Formats

Selecting “Save As,” you can also select from the following Excel formats:
  • Excel Workbook creates an Excel .xlsx file.
  • Excel Macro-Enabled Workbook creates an Excel .xlsm file.
  • Excel Binary Workbook creates an Excel .xlsb file.
  • Excel 97–2003 Workbook creates an Excel .xls file.

SAS Import and Export Utilities Support for Excel Files

Overview

SAS Import and Export Utilities provide three methods to access Microsoft Excel files.
LIBNAME statement
generates a LIBNAME statement and SQL commands for the PC files engine. Read data from or write data to an Excel file. This method supports Excel versions 5, 95, 97, 2000, 2002, 2003, and 2007.
See Overview: LIBNAME Engines for additional information.
Note: DBMS=EXCEL and DBMS=EXCELS use this method to access data in Excel files.
ACCESS and DBLOAD procedures
generate ACCESS procedure code to read data from an Excel file. Also generate the DBLOAD procedure code to write data to an Excel file. This method supports only Excel versions 4 and 5/95. This is for SAS 6 compatibility support and is available only on Microsoft Windows.
Note: DBMS=EXCEL5 uses this method to access data in Excel files.
XLS and XLSX file formats
translates Excel .xls or .xlsx file formats to read data from or write data to an Excel file. This component supports Excel versions 5/95, 97, 2000, 2002, and 2003.
Note: This method does not support Excel .xlsb files, and it does not support such DBCS character sets as Chinese, Japanese, or Korean.
Note: DBMS=XLS and DBMS=XLSX use this method to access data in Excel files.
See SAS/ACCESS Descriptors for PC Files.

Import and Export Microsoft Excel Files Using the LIBNAME Statement

The LIBNAME statement method for importing and exporting Microsoft Excel workbook files generates SAS LIBNAME statement code. The LIBNAME statement uses the Microsoft ACE engine or Microsoft Jet engine to access data in Microsoft Excel workbook files.
Statement Options to Import or Export Excel Data
Data Source
Syntax
Valid Value
Default Value
PROC IMPORT
PROC EXPORT
EXCEL
DBDSOPTS
Excel data set options
Yes
Yes
DBSASLABEL
Compat | None
Compat
Yes
No
GETNAMES
Yes | No
Yes
Yes
No
MIXED
Yes | No
No
Yes
No
NEWFILE
Yes | No
No
No
Yes
RANGE
range name
Yes
No
SCANTEXT
Yes | No
Yes
Yes
No
SCANTIME
Yes | No
Yes
Yes
No
SHEET
sheet name
Yes
Yes
TEXTSIZE
1 to 32767
1024
Yes
No
USEDATE
Yes | No
Yes
Yes
No
The next table lists Statement Options to Import or Export Excel Data files on Linux, Windows, and UNIX. This method requires that the PC Files Server is running on a Microsoft Windows operating system where the PC files reside.
Statement Options to Import or Export Excel Data Files on Windows, Linux, and UNIX
Data Source
Syntax
Valid Value
Default Value
PROC IMPORT
PROC EXPORT
EXCELCS
DBDSOPTS
'Excel data set options'
Yes
Yes
DBSASLABEL
Yes | No
Yes
Yes
No
PORT
1 to 65535
9621
Yes
Yes
RANGE
'range name'
Yes
No
SCANTEXT
Yes | No
Yes
Yes
No
SCANTIME
Yes | No
Yes
Yes
No
SERVER
'server name'
Yes
Yes
SERVERPASS
'server password'
Yes
Yes
SERVERUSER
'server User ID'
Yes
Yes
SERVICE
'service name'
Yes
Yes
SHEET
'sheet name'
Yes
Yes
SSPI
Yes | No
No
Yes
Yes
TEXTSIZE
1 to 32767
1024
Yes
No
USEDATE
Yes | No
Yes
Yes
No
VERSION
'5' | '95' | '97' | '2000' | '2002' | '2003' | '2007'
'97'
Yes
Yes
DBDSOPTS= valid data set options for the SAS Excel LIBNAME engine
enables data set options for the LIBNAME engine such as READBUFF, INSERTBUFF, DBTYPE, DROP, FIRSTOBS, and OBS. These options are for advanced users who are familiar with the PC Files LIBNAME engine.
Notes:To improve performance for reading data, set the READBUFF= option to 25 or higher.

Enclose the options in single or double quotation marks as shown in the examples.

If the option string that you specify contains single quotations marks, use double quotation marks around it in your statement.

Example:DBDSOPTS Examples
DBDSOPTS= 'FIRSTOBS=10 READBUFF=25';
DBDSOPTS= "DBTYPE=(BilledTo='CHAR(8)')";
DBSASLABEL= COMPAT | NONE | YES | NO
specifies the data source for column names.
COMPAT specifies that the data source column headings are saved as the corresponding SAS label names.
Alias: YES
NONE specifies that the data source column headings are not saved as SAS label names. The SAS label names are then left as blanks.
Alias: NO
Restrictions:Due to Microsoft Jet engine and Microsoft ACE engine limitations, no more than 64 characters of column names are written to SAS variable labels.

Due to Microsoft Jet engine and Microsoft ACE engine limitations, using MIXED=YES could result in improper text variable lengths.

GETNAMES= YES | NO
specifies whether the IMPORT procedure is to generate SAS variable names from the data values in the first row of the import file.
If data in the first row of the input file contains special characters for a SAS variable name (such as a blank), SAS converts the character to an underscore.
YES specifies that the IMPORT procedure generate SAS variable names from the data values in the first row of the imported Excel file.
NO specifies that the IMPORT procedure generate SAS variable names as F1, F2, F3, and so on.
Default:YES
Restrictions:Valid only for Windows.

Valid only for the IMPORT procedure.

Supported only when DBMS=EXCEL.

When SAS reads the data value in the first row of the input file, SAS checks for invalid SAS name characters (such as a blank). Invalid characters are converted to an underscore. For example, the data value Occupancy Code becomes the SAS variable name Occupancy_Code.

MIXED= YES | NO
assigns a SAS character type for the column and converts all numeric data values to character data values when mixed data types are found.
YES specifies that the connection is set to import mode and updates are not allowed.
Note: Due to a limitation in the Microsoft ACE engine and the Microsoft Jet Excel engine, using 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.
Note: Numeric data in a character column and character data in a numeric column are imported as missing values.
Default: NO
Restriction: Supported only when DBMS=EXCEL.
Interaction:The ‘TypeGuessRows’ entry in your registry settings can affect the behavior of the MIXED= option. The options are located in a key of the Microsoft Windows registry.
To change the value of ‘TypeGuessRows’ in your registry, follow these steps:
  1. Access the Registry Editor by either selecting Start then selectRun from your desktop, entering Regedt and selecting OK, or by following the instructions for your system found in your system or application documentation.
    Registry Editor
  2. Open the appropriate key in the Registry Editor window, as indicated in the table below.
    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
  3. In the right pane, double-click TypeGuessRows.
  4. Change the Value data entry from 8 to 0 and select OK.
    TypeGuessRows Registry Entry
  5. Exit from the Registry Editor window.
The following table describes the registry settings for the MIXED= option.
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 up to 16384 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 causes a scan to fail if you set it higher than 16384. It also affects any software that uses the Microsoft Ace 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 recommended that you set the value to 0.
ImportMixedTypes
A string type with a default value of Text. If a column contains more than one type of data while scanning TypeGuessRows rows, the column type is determined to be Text if the setting value is Text. If the setting value is Majority Type, the most common column type determines the column type.
For the MIXED= YES option to work correctly, you should change TypeGuessRows 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 Microsoft Ace engine and the Microsoft Jet engine always assign character type for columns with data of mixed data types.
The numeric data is converted to character data.
NEWFILE= YES | NO
when exporting a SAS data set to an existing Excel file, specifies whether to delete the Excel file, and load the data to a sheet in a new Excel file.
YES specifies that the EXPORT procedure deletes the specified Excel file, if it exists. Loads the SAS data set to a sheet in a new Excel file.
NO specifies that the EXPORT procedure loads the SAS data set to a sheet and appends it to the existing Excel file. If the specified Excel file does not exist, an Excel file is created, and the SAS data set is loaded.
Restriction: Available only when DBMS=EXCEL.
PORT= port-number
specifies the number of the port that is listening on the PC Files Server. The valid value is between 1 and 65535. This port or service name displays on the PC Files Server display when the application is started in server mode.
Alias:PORT_NUMBER
Default: 9621
Restrictions: Available only for the client/server model.

The PORT= statement option and the SERVICE= statement option should not be used in the same procedure.

RANGE= range-name | absolute-range
subsets a spreadsheet by identifying the rectangular set of cells to import. The range- name is a user-defined spreadsheet name that represents a range of cells within the spreadsheet in the Excel file. The range-name is not case sensitive and does not allow any special character (except an underscore). The range-name is identified by the top left cell that begins the range and the bottom right cell that ends the range within the Excel worksheet file. The beginning and ending cells are separated by two periods. For example, the range address C9..F12 indicates a cell range that begins at Cell C9, ends at Cell F12, and includes all cells in between. You must define range-name with a workbook scope so that the name is visible to SAS.
You can use the DATASETS procedure to list the data set names, that are mapped to the range-names. If the displayed range-name contains single quotes, keep the single quotes as part of the range-name to access the sheet.
An absolute range identifies the top left cell that begins the range and the bottom right cell that ends the range.
The following examples demonstrate the use of RANGE=.
  • To retrieve data from the spreadsheet for two separate sheet names, 'My#Test' and 'CustomerOrders': 
    RANGE="'My#Test$'"; 
RANGE="' CustomerOrders$'";
  • To represent cells within Column C, Row 2, and Column F, Row 12: C2:F12 the colon separates the values for upper left (UL) and lower right (LR) of the range. If this statement is not specified, the IMPORT procedure reads the entire spreadsheet as a range.
  • When data is imported from an Excel file, a sheet name that is appended with a $ character is treated as a range name. The range name refers to the whole sheet. RANGE="'summary$a4:b20'";
  • If the range name is available, it is recommended that you use RANGE = option without the SHEET option for the IMPORT procedure. To use the absolute range address, it is strongly recommended that you use the full range address with quotes. For example, specify 'sheet_name$A1:C7'n
Restriction: Supported only for the IMPORT procedure.
SCANTEXT= YES | NO
specifies whether to scan the column to determine the length of the text data for each data source column. This option applies only to character data type columns.
YES scans the length of text data for a data source column and uses the length of the longest string of data that it finds as the SAS variable width. If the maximum length that it finds is greater than the value in the TEXTSIZE = option, the smaller value in TEXTSIZE = option is applied as the SAS variable width.
NO does not scan the length of text data for a data source column. The column length returned from the Microsoft Ace or Microsoft Jet engine is used as the SAS variable width. If the returned column width is greater than what is specified in the TEXTSIZE option, the smaller value specified in the TEXTSIZE= option is applied as the SAS variable width.
Note: Specify SCANTEXT= NO when you need to update data in a Microsoft Excel workbook.
Note: So that the SCANTEXT= YES option works correctly, it is strongly recommended that you change TypeGuessRows to 0 in the Microsoft Windows registry. This ensures that all rows in the specified range are scanned. For registry values, see Registry Key for TypeGuessRow Based on Office Version or Engine.
Note: To correct truncated text data for DBCS users (including Chinese, Japanese, and Korean), you can set the environment variable DBE_DBCS to YES. This doubles the scanned text data length and therefore corrects the problem of truncated text data. To set the DBE_DBCS environment variable, submit this statement: Options set=DBE_DBCS YES;
Note: The Microsoft ACE or Microsoft Jet engine handles the SCANTEXT= option.
Note: These TypeGuessRows and ImportMixedTypes registry settings could affect the behavior of the SCANTEXT= option. Refer to the Type Guessrows and Mixed Type Data table for additional information.
Alias: SCAN_TEXT | SCANMEMO
Type Guessrows and Mixed Type Data
TypeGuessRows
An integer type with a default value of 8. The number of rows in the worksheet range is used to scan to determine column types. If set to 0, all rows in the range are checked. Microsoft states that the valid range of TypeGuessRows is 0 to 16. However, it could be set as high as 16384 and still operate correctly.
ImportMixedTypes
A string type with a default value of Text. If a column contains more than one data type when scanning of TypeGuessRows rows, the column type is determined to be Text if the setting value is Text. If the setting value is Majority Type, the most common column type determines the column type.
SCANTIME=YES | NO
specifies whether to scan the time data while importing data from a time column from the Microsoft Excel workbook.
YES scans the time column and assigns the TIME. format for a time column.
NO specifies to not scan the time column. The DATETIME format is assigned if USEDATE= NO. The TIME. format is assigned if USEDATE= YES.
SERVER= PC-Files- Server-name
specifies the name of the PC Files Server, where PC-Files-Server-name can be either the computer name or the associated IP address. You must bring up the listener on the PC Files Server before you can establish a connection. You can configure the service name, port number, maximum number of concurrent connections allowed, and data encryption on your PC Files Server.
Alias: SERVER_NAME
Restriction: Available only for client/server model.
Note: You can omit this option if you are running SAS and the PC Files Server on the same machine. Omitting this option under this condition causes the PC Files Server to start automatically in the background.
SERVERPASS= server-user-password
specifies the password for the User ID given. If the account has no password, omit this option. Always enclose the value in quotes, this preserves the case of the password.
Alias: SERVERPASSWORD | SERVERPW | SERVERPWD
Notes: Passwords are generally case sensitive.

Use the PASSWORD= option for database passwords.

Example:LIBNAME using explicit user name and password, for PC Files Server:
    LIBNAME DB PCFILES PATH=’C:\myfile.mdb’
        SERVER=fileserv;
        SERVERUSER=’mydomain\myusername’;
        SERVERPASS=’mypassword’;
Tip
If you are not on a domain, omit the domain name and the backslash.
SERVERUSER= server-user-name
specifies a domain and User ID that is valid for the PC running PC Files Server. Always enclose the value in quotes. Otherwise, the backslash can be misinterpreted by the SAS parser.
Alias: SERVERUID
Notes: If you are not on a domain, omit the domain name and the backslash.

Use the USER= option for database user IDs.

Example:Here is an example of the LIBNAME statement using explicit user name and password, used with the PC Files Server.
    LIBNAME DB PCFILES PATH=’C:\myfile.mdb’
        SERVER=fileserv;
        SERVERUSER=’mydomain\myusername’;
        SERVERPASS=’mypassword’;
Tip
If you are not on a domain, omit the domain name and the backslash.
SERVICE= service-name
specifies the service name that is defined on your service file for your client and server machines. This port number or service name is displayed on the PC Files Server control panel screen when it is started on the PC. The service name needs to be defined on your UNIX machine and your PC Files Server.
Alias:SERVICE_NAME
Restrictions: Available only for the client/server model.

The SERVICE= statement option and the PORT= statement option should not be used in the same procedure.

SHEET= sheet-name
identifies a particular worksheet in an Excel workbook. Use the SHEEToption only when you want to import an entire worksheet. The sheet-name can contain up to 31 characters. If the EXPORT procedure sheet-name contains special characters (such as space) SAS converts it to an underscore.
The following examples demonstrate how SAS converts non-compliant sheet names.
  • The space is converted to an underscore. Employee Information becomes 
    Employee_Information
  • If the sheet name contains single quotes, keep the single quotes as part of the sheet name in order to be able to access the sheet. SHEET="'My#Test'";
Restriction: Avoid sheet names that look like cell references, which have <1–3 characters> plus 1 or more digits. For example, A1, IV65536, TRY123, XFD1048576.
Notes: It is recommended that you use the RANGE= option without the SHEET= option in the IMPORT procedure.

If both the range name and the sheet name are missing, the IMPORT procedure reads the first worksheet that was physically saved in the Excel file.

SSPI= YES | NO
enables the server administrator to allow Integrated Windows Authentication. This is a mechanism for the Windows client and server to exchange credentials.
Default: NO
Restriction: Valid only on a 64-bit Windows PC.
Note: SSPI can also be enabled by specifying the –SSPI option on the SAS command line.
TEXTSIZE= 1 to 32767
specifies the SAS maximum variable length that is allowed while importing data from Microsoft Excel text columns. Any text data in Excel whose length exceeds this value is truncated when it is imported into SAS.
Alias: DBMAX_TEXT
USEDATE= YES | NO
specifies whether to assign a DATE format while importing a date column from a Microsoft Excel workbook.
YES specifies the DATE9. format for the corresponding date column in the Microsoft Excel table.
NO does not specify the DATE9. format for the corresponding date column in the Microsoft Excel table.
See: SCANTIME= statement to assign the appropriate TIME format.

Processing Date and Time Values between SAS and Microsoft Excel

VERSION= file-version
specifies the version of the file that you want to create. Valid values are 2007, 2003, 2002, 2000, 97, 95, and 5.
Default: 97 for .xls files
Restrictions: If the file exists on the PC Files Server, this statement is ignored.

Available only for client/server model.

Note: Always enclose the version in single quotes.

Example 1: Export a SAS Data Set to an Excel File

Export a SAS data set called SDF.ORDERS to an Excel 2007 .xlsb file with the Orders sheet name. In this case, SHEET= supports only an .xlsb file. 
LIBNAME SDF "&sasdir";
PROC EXPORT DATA=SDF.ORDERS
        FILE='c:\temp\demo.xlsb'
        DBMS=EXCEL REPLACE;
    SHEET='Orders';
RUN;

Example 2: Import a Range from Excel 2007 File to a SAS Data Set

This example imports a range named INVOICE from an Excel workbook file and performs these tasks:
  • uses the first row of data as column names
  • scans length for character type columns
  • assigns DATE9. or TIME8. format to date and time columns respectively
  • leaves SAS labels blank
  • limits the size of text fields to be less than or equal to 512 characters 
PROC IMPORT OUT= WORK.INVOICE
        FILE= "&demodir.demo.xlsb"
        DBMS= EXCEL REPLACE;
    RANGE= 'INVOICE';
    GETNAMES= YES;
    SCANTEXT= YES;
    USEDATE= NO;
    SCANTIME= YES;
    DBSASLABEL= NONE;
    TEXTSIZE=512;    /* default is 1024 */
	RUN;

Example 3: Import a Range from an Excel File on a PC Files Server to a SAS Data Set

The code in this example imports a range named Orders from an Excel file on a PC Files Server. It assigns the DATE9. format to date columns. The TIME8. format is assigned to time columns. 
PROC IMPORT OUT=WORK.ORDERS
        DATAFILE="&pcfdir.demo.xls"
        DBMS=EXCELCS REPLACE;
    RANGE=’Orders’;
    SERVER="&server";
    USEDATE=NO;
    SCANTIME=YES;
RUN;

Example 4: Export a SAS Data Set to an Excel Workbook File

This code exports a SAS data set to an Excel workbook file. The Excel file is on a UNIX or Windows 64-bit server with the “Customer” sheet on the PC that is running PC Files Server. 
LIBNAME SDF "&sasdir";
PROC EXPORT DATA=SDF.CUSTOMER;
        FILE=’c:\temp\demo.xls’
        DBMS=EXCELCS REPLACE;
    SHEET=’Customer’;
    SERVER="&server";
    SERVICE=SASPCFILE;
RUN;

Example 5: The IMPORT Procedure Using SSPI — Microsoft 64–Bit Windows 

PROC IMPORT OUT=work.test
        DATAFILE=’C:\myFile.xlsb’
        DBMS=EXCELCS
        REPLACE;
    SERVER=FILESRV;
    SSPI=YES;
RUN;

Importing and Exporting Microsoft Excel 4 and Excel 5 Files

The IMPORT and EXPORT methods use the ACCESS and DBLOAD procedures to access data in Microsoft Excel files. Excel 2007, .xlsx, .xlsb, and .xlsm files are not supported. The ACCESS and DBLOAD procedures are available only on Microsoft Windows.
Note: Because the ACCESS and DBLOAD procedures are compatible only with SAS 6 procedures, they ignore SAS system options such as the VALIDVARNAME= option. The ACCESS procedure and the DBLOAD procedure have other SAS 6 limitations such as a maximum of 8-byte SAS variable names and a maximum of 200-character value.
The following table lists the statements that are available to import data from or export data to an Excel file using the EXPORT and IMPORT procedures.
Statements for Importing and Exporting Excel 4 and Excel 5 Files
Data Source
Syntax
Valid Value
Default Value
PROC IMPORT
PROC EXPORT
EXCEL5
GETNAMES
Yes | No
Yes
Yes
No
RANGE
Range Name or Absolute Range Value, such as ’A1..C4’
Yes
No
SHEET
Sheet name
Yes
No
EXCEL4
GETNAMES
Yes | No
Yes
Yes
No
RANGE
Range Name or Absolute Range Value, such as ’A1..C4’
Yes
No
GETNAMES= YES | NO
specifies whether the IMPORT procedure is to generate SAS variable names from the first record of the Microsoft Excel import file.
If data in the first record of the input file contains special characters for a SAS variable name (such as a blank), SAS converts the character to an underscore. For example, the data value MY ID becomes the SAS variable name MY_ID.
YES specifies that the IMPORT procedure generate SAS variable names from the data values in the first record of the imported Excel file.
NO specifies that the IMPORT procedure generate SAS variable names as F1, F2, F3, and so on.
Default:YES
Restrictions:Valid only for Windows.

Valid only for the IMPORT procedure.

Supported only when DBMS=EXCEL5.

When SAS reads the data value in the first row of the input file, SAS checks for invalid SAS name characters (such as a blank). Invalid characters are converted to an underscore. For example, the data value Occupancy Code becomes the SAS variable name Occupancy_Code.

RANGE= range-name | absolute-range
subsets a specified section of an Excel file worksheet. The range- name is the name that is assigned to a range address within the worksheet. Range names are not case sensitive. The range-address is identified by the top left cell that begins the range and the bottom right cell that ends the range within the Excel worksheet file. The beginning and ending cells are separated by two periods. For example, the range address C9..F12 indicates a cell range that begins at Cell C9, ends at Cell F12, and includes all cells in between.
SHEET= sheet-name
identifies one worksheet from a group of worksheets while you are reading from an Excel file. The sheet name can be up to 31 characters. The SHEET statement is optional.

Example 1: Import a SAS Data Set to an Excel 5 File

This example imports a SAS data set, INVOICE, from an Excel 5 workbook file, INVOICE. 
PROC IMPORT OUT=WORK.INVOICE
        FILE="&xls5dir.invoice.xls"
        DBMS=EXCEL5 REPLACE;
    GETNAMES=yes;
RUN;

Example 2: Export a SAS Data Set to an Excel 5 File

This example exports a SAS data set, ORDERS, to an Excel 5 workbook file. 
LIBNAME SDF "$sasdir";
PROC EXPORT DATA=SDF.ORDERS
OUTFILE=’c:\temp\orders.xls’

Import and Export Microsoft Excel Files Using XLS and XLSX File Formats

This Import/Export component uses the translation engine method to read and write XLS file formats directly. This component supports Excel versions 5/95, 97, 2000, 2002, and 2003. However, it does not support Excel 2007 .xlsx, .xlsb, or .xlsm files. It is available on Linux, UNIX, and Microsoft Windows operating platforms.
These tables list the statements that are available to import data from or export data to an Excel file.
Available Statements for Importing and Exporting Excel Files Using the Translation Engine
Data Source
Syntax
Valid Value
Default Value
PROC
IMPORT
PROC
EXPORT
XLS
ENDCOL
Last column for data
Last column that contains data
Yes
No
ENDNAMEROW
Last row for variable names
Same as NAMEROW
Yes
No
ENDROW
Last row for data
Last row that contains data
Yes
No
GETNAMES
Yes | No
Yes
Yes
No
NAMEROW
First row for variable names
First row that contains variable names
Yes
No
NEWFILE
Yes | No
No
No
Yes
PUTNAMES
Yes | No
Yes
No
Yes
RANGE
NAME | SHEET$UL-LR
First row
Yes
No
SHEET
Sheet name
First sheet
Yes
Yes
STARTCOL
First column for data
Last column that contains data
Yes
No
STARTROW
First row for data
First row that contains data
Yes
No
XLSX
GETNAMES
Yes | No
Yes
Yes
No
RANGE
NAME | SHEET$UL-LR
First row
Yes
No
SHEET
Sheet name
First sheet
Yes
No
ENDCOL= last-column-for-data
specifies the last column for data
Default: The last row that contains data.
Restriction: Available only for DBMS=XLS for backward compatibility.
ENDNAMEROW= name-row
specifies the last row for variable names.
Default: The same as NAMEROW
Restriction: Available only for DBMS=XLS for backward compatibility.
ENDROW= 1 to 65535
specifies the last row for data.
Default: The last row that contains data.
Restriction: Available only for DBMS=XLS for backward compatibility.
Note: Valid Value Range: 1 to 65535
GETNAMES= YES | NO
determines whether to generate SAS variable names from the data values in the first record of the imported file. If data in the first record is read and it contains characters that are not valid in a SAS name, SAS converts the character to an underscore.
For example, the data value Occupancy Code becomes the SAS variable name Occupancy_Code.
YES specifies that PROC IMPORT is to generate SAS variable names from the data values in the first record of the imported Excel file.
NO specifies that PROC IMPORT is to generate SAS variable names as VAR1, VAR2, VAR3, and so on.
Restrictions: Available only for DBMS=XLS for backward compatibility.

PROC IMPORT only

NAMEROW= name-row
specifies the first row for variable names.
Default: The first row that contains variable names.
Restriction: Available only for DBMS=XLS for backward compatibility.
NEWFILE= YES | NO
when exporting a SAS data set to an existing Excel file, specifies whether to delete the Excel file, and load the data to a sheet in a new Excel file.
YES specifies that the EXPORT procedure deletes the specified Excel file, if it exists. Loads the SAS data set to a sheet in a new Excel file.
NO specifies that the EXPORT procedure loads the SAS data set to a sheet and appends it to the existing Excel file. If the specified Excel file does not exist, an Excel file is created, and the SAS data set is loaded.
Restriction: Available only for DBMS=XLS for backward compatibility.
PUTNAMES= YES | NO
determines whether to write SAS variable names to the first record of the exported data file. If you specify the LABEL option, SAS variable labels are written instead of variable names.
YES specifies that PROC EXPORT is to write SAS variable names to the first record and write the first observation data to the second record of the exported data file.
NO specifies that PROC EXPORT is to write the first observation data to the exported data file.
Restrictions: Available only for DBMS=XLS for backward compatibility.

PROC EXPORT only.

RANGE= ‘range-name’ | ‘absolute-range’;
subsets a spreadsheet by identifying the rectangular set of cells to import from the specified spreadsheet. The range name is a name that represents a range of cells within the spreadsheet in the Excel file. Absolute range identifies the top left cell that begins the range and the bottom right cell that ends the range. For example, ’C2..F12’ represents cells within column C, row 2, and column F, row 12. You need to specify the target sheet name with SHEET= if you use absolute range.
By default, the first row is viewed as containing variable names. If GETNAMES=N, the column is set to the 1- or 2-letter ID, and all rows are read as data.
If you do not specify RANGE=, PROC IMPORT reads the entire worksheet as a range.
Use RANGE= instead of STARTCOL=, STARTROW=, ENDCOL=, ENDROW=, or any combination of these because RANGE= already contains all of these values.
Restriction: This statement is not valid for PROC EXPORT.
SHEET= ’sheet-name’
identifies a particular worksheet in an Excel workbook. Specify sheet-name to name the sheet name as output. If the sheet already exists, it is replaced. You can also use it to append a new sheet to an existing worksheet.
If you do not specify this statement, PROC IMPORT reads the first worksheet physically saved in the Excel file. To be certain that PROC IMPORT reads the worksheet that you want, specify SHEET= to identify the worksheet.
Even if you can specify the SHEET= statement in PROC IMPORT for EXCEL4, the value is ignored. Excel version 5/95 allows multiple sheets in a file, but this export component supports only exporting a single sheet per file.
For PROC EXPORT, if you specify the SHEET= statement, the name defines the sheet name and range name in the exported Excel file. The underscore character replaces the special character for both the range and sheet names. If you do not specify the SHEET= statement, the SAS data set name defines the sheet name and range name in the exported Excel file.
STARTCOL= start-column
specifies the first column for data.
Default: The first column that contains data.
STARTROW= start-row
specifies the first row for data.
Default: The first row that contains data.
Restriction: Available only for DBMS=XLS for backward compatibility.

Example 1: Export SAS Data Sets to Excel Workbook Files

This example exports the SAS data sets, SDF.INVOICE and SDF.ORDERS, to Excel workbook files with Invoice and Orders as sheet names. 
LIBNAME SDF V9 "&sasdir" 
PROC EXPORT DATA=SDF.INVOICE
        FILE="&tmpdir.text.xlx"
        DBMS=XLS REPLACE;
    SHEET=’Invoice’;
RUN;

PROC EXPORT DATA=SDF.ORDERS
        FILE="&tmpdir.text.xls"
        DBMS=XLS REPLACE;
    SHEET=’Orders’;
RUN;

Example 2: Import Data Using a Range Name

This example imports SAS data from a demo XLS file using a range name. 
PROC IMPORT OUT=WORK.INVOICE
        FILE="&demodir.demo.xls"
        DBMS=XLS REPLACE;
    RANGE=’INVOICE’;
    GETNAMES=YES;
RUN;

Example 3: Import Data Using an Absolute Range Address

This example imports SAS data from a demo XLS file using an absolute range address. 
PROC IMPORT OUT=WORK.INVOICE
        FILE="&demodir.demo.xls"
        DBMS=XLS REPLACE;
    RANGE="Invoice$B4:D10";
    GETNAMES=NO;
RUN;
Copyright © SAS Institute Inc. All rights reserved.