search: Search support.sas.com KNOWLEDGE BASE     System Requirements     Install Center     Product Documentation     Papers     Samples & SAS Notes     Focus Areas SUPPORT     License Assistance     Administrator Services     Downloads & Hot Fixes LEARNING CENTER     Bookstore     Training     Certification     SAS Learning Edition     Higher Education Resources     SAS OnDemand For Academics COMMUNITY     User Groups     Events     E-Newsletters

• Print |
• E-mail  |
• Bookmark

The EXCEL and XLS engines do not support importing and exporting of Microsoft Office 2007 files. These files have an extension of .xlsx, which is based on the Open XML standard. This type of file also allows for 16,384 columns and 1,048,576 rows.

Support for Office 2007 files is available in SAS^® 9.2 Phase 2 (TS2M0). Contact SAS Customer Service, your account team, or SAS Technical Support to request the Phase 2 software.

SAS Representatives at a company site can request the software from the link listed in SAS Note 35445, "Process to Request SAS^® Phase 2.

In the meantime, you can use the following methods to import, export, and create Office 2007 fies in SAS 9.1.3 and SAS9.2 Phase 1.

Methods for Importing and Exporting Excel 2007 Files

• Use the existing XLS formats

  With Office 2007, you can convert Excel .xlsx files to Excel .xls files, which the EXCEL and XLS engines can use. To convert a .xlsx file:

  1. Select File ► Save As in the file that you want to convert.
  2. In the Save as type field, select Microsoft Office Excel Workbooks (*.xls).

  Note: You can save the fie as a .xls file in Office 2007 as long as your file does not exceed 255 columns and 65,000 rows.

  You can also export using these engines and open the .xls file in Excel 2007.

• Use SAS/ACCESS^® Interface to ODBC with the Excel 2007 ODBC driver

  Microsoft use Open Data Base Connectivity (ODBC) as a standard to reach several different database types, including Excel 2007 files. You can set this up as follows:

  1. Open your ODBC administrator from your control panel:
         Start ► Settings ► Control Panel ► Administrative Tools
  2. Double-click Data Sources (ODBC) to open the ODBC Data Source Administrator dialog box.
  3. Select the System DSN tab and click AddSelect Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).
  4. Click Finish, and the ODBC Microsoft Excel Setup dialog is displayed. In the Data Source Name field, type in the name that you want (for example, myexcel2007).
  5. Click the Select Workbook button and select the .xls or .xlsx file that you want. Note: If you want to write to the database, make sure the Read Only check box is not checked.
  6. Then, submit the following statements in SAS:
     libname odbclib odbc dsn='myexcel2007'; data mydata; set odbclib.'baseball$'n; run;

     In this example, BASEBALL is the name of the Excel worksheet or table that you want to read in the .xlsx file. If you do not want to go through a menu each time, you can submit the following statements:

     libname myxls odbc required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; dbq=C:\excelfiles\air.xlsx";

     The DBQ= option specifies the path where your .xlsx file is located.

  To update an Excel spreadsheet, you must disable the READONLY= option, which is on by default for the Microsoft ODBC driver. To replace a table in the spreadsheet, you must drop it first, as shown in this example:

  libname myxls odbc required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; dbq=C:\excelfiles\air.xlsx; readonly=false"; proc sql; drop table myxls.baseball; quit;

  Then you can add a new table to the spreadsheet, as shown in this example:

  data myxls.baseball; set sasuser.baseball; run;
• Use SAS/ACCESS^® Interface to OLEDB

  In SAS/ACCESS Interface to OLEDB, you can submit the following code to read or to create an .xlsx file with the Microsoft Office 2007 OLEDB.

  libname boo oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\excelfiles\air.xlsx; extended Properties=Excel 12.0"; data new; set boo.'air3'n; run;

  The location path in the DATA SOURCE= statement is where your .xlsx file is located.

  You can also create tables with this method, but you cannot replace tables with this method. To create tables, you can submit statements similar to the following:.

  data boo.'air3'n; set sashelp.air; run;

  However if you try to replace a table, the following error occurs:

  ERROR: The OLEDB table air2 has been opened for OUTPUT. This table already exists, 
  or there is a name conflict with an existing object. This table will not be replaced.
  This engine does not support the REPLACE option.
  

  To replace a table, you must first drop the table by using the SQL procedure, as shown here:

  proc sql; drop table boo.'air3'n; quit; data boo.'air3'n; set sashelp.air; run;
• Using Dynamic Data Exchange (DDE) to read from and write to Excel 2007 files

  DDE is an old technology, but it still works and you can use it to read from and write to Excel workbooks. However, using this technology requires some programming. You should limit the size of your data to 20,000 cells or less, depending on your memory constraints. The following example is a small program that writes to an Excel sheet named Sheet1:

  filename test dde 'Excel|[Book1]Sheet1!R1C1:R19Cc'; data _null_; set sashelp.class; file test notab; put name '09'x age '09'x height '09'x weight '09'x sex; run;

  This next example is a small program that reads from an Excel worksheet.

  data temp1; infile test missover notab dsd dlm='09'x; input name $ age height weight sex $; run;

  For more information, see "Using Dynamic Data Exchange under Windows" in SAS^® OnlineDoc 9.2.

Import-Only Methodology

You can save your Excel worksheet as a tab-delimited file and then import that file into SAS, as follows:

1. In your Excel worksheet, select File ► Save As ► Other Formats.
2. Then select Text (Tab Delimited) from the Save As type list. The application prompts you with a message that it will only save the current sheet as this type.
3. You can then submit the following procedure to import your file:
   proc import datafile='your-files.txt' out=test dbms=tab; run;

Export-Only Methodologies

• Use ODS tagsets to create multiple sheets

  You can use the Output Delivery System (ODS) to generate multiple sheets. Click here for code to accomplish this task.

  You can use ODS in several ways, one of which it to use the ExcelXP tagset. See SAS Note #32394 for the installation of this tagset.

• Use the XML engine to create multiple sheets

  To export multiple sheets in to Excel:

  1. Use the XML engine to create the worksheets, as follows:
     libname test xml 'C:\my-file.xml'; data test.mysheet1; set sashelp.class; run; data test.mysheet2; set sasehelp.air; run;
  2. Then, open the XML file into Excel and resave it as an .xlsx file.

• Use the SAS Explorer Window

  You can use the SAS Explorer window to open any SAS data set into Excel 2007. Simply right-click the SAS data set in the SAS Explorer and select View in Excel.

  This selection opens the resulting HTML file into Excel. When Excel 2007 attempts to open the HTML file, the following message is generated:

  The file you are attempting to open is in a different format
  than specified by the file extension. Verify that the file is from a 
  trusted source before opening the file.  Do you want to open the file? 
  

  Click YES and the file will open in Excel.

• Use tab-delimited files

  Tab-delimited files can be exported into Excel. You can create a tab-delimited file by submitting the following statements:

  proc export data=sashelp.class outfile='my-file.txt' dbms=tab; run;

  You can then import the tab-delimited text file into your workbook.

Operating System and Release Information

Product Family	Product	System	SAS Release
			Reported	Fixed
SAS System	SAS/ACCESS Interface to PC Files	Microsoft Windows XP Professional	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows NT Workstation	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3 SP4	9.2 TS1M0
		Windows Vista	9.1 TS1M3 SP4	9.2 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

This note shows all of the ways you can import an Excel 2007 file of type .xlsx. Most of these methods are Windows system methodologies. Importing and exporting Microsoft Office 2007 files is supported in SAS® 9.2 Phase 2.

Type:	Problem Note
Priority:	medium
Topic:	Data Management ==> Data Sources ==> External Databases ==> MS Excel

Date Modified:	2009-04-09 10:22:46
Date Created:	2008-06-16 16:00:35