Importing and Exporting Microsoft Excel Files with the ACCESS and DBLOAD Procedures

The IMPORT and EXPORT components use the ACCESS and DBLOAD procedures behind the scenes to access data in Microsoft Excel files. Excel 2007, .xlsx, .xlsb, or .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 ACCESS and DBLOAD procedures. For examples of these statements, see ACCESS and DBLOAD Procedures Options.

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. For example, the column heading.

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
Restriction:	Valid only for Windows.
Restriction:	Valid only for the IMPORT procedure.
Restriction:	Valid only when DBMS=EXCEL.
Restriction:	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 .

Example 1: Export SAS Data Sets to Excel Workbook Files

This code exports the SDF.INVOICE and SDF.ORDERS SAS data sets to Excel workbook files with Invoice and Orders as sheet names.

LIBNAME SDF V9 "&sasdir";
PROC EXPORT DATA=SDF.INVOICE
           FILE="&tmpdir.text.xls"
            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 code imports SAS data from an .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

Import SAS data from an .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 © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.