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.
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:
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.
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:
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:
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:
Then you can add a new table to the spreadsheet, as shown in this example:
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.
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:
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:
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:
This next example is a small program that reads from an Excel worksheet.
For more information, see "Using Dynamic Data Exchange under Windows" in SAS® OnlineDoc 9.2.
You can save your Excel worksheet as a tab-delimited file and then import that file into SAS, as follows:
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.
To export multiple sheets in to Excel, follow these steps:
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.
Tab-delimited files can be exported into Excel. You can create a tab-delimited file by submitting the following statements:
You can then import the tab-delimited text file into your workbook.
|Product Family||Product||System||SAS Release|
|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|
|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|