The EXCEL and XLS engines do not support the 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 TS2M0. Contact SAS Customer Service, your account team, or SAS Technical Support to request the SAS 9.2 TS2M0 software.

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

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 file as an .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 uses Open Database Connectivity (ODBC) as a standard to reach several 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. Click the System DSN tab and click Add to open the Create New Data Source dialog box.
  4. Select Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).
  5. Click Finish, and the ODBC Microsoft Excel Setup dialog box is displayed. In the Data Source Name field, type in the name that you want (for example, myexcel2007).
  6. 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 that the Read Only check box is not checked.
  7. 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, 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;
  8. 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;
  9. 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 only the current sheet will be saved 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, follow these steps:

  1. Use the XML engine to create the worksheets, as shown here:
     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 in 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 in 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 in 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. The file opens 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 that 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 TS2M2.

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

Date Modified:	2010-02-17 16:07:34
Date Created:	2008-06-16 16:00:35