Since its 4.2 release, SAS Enterprise Guide has been able to import Microsoft Excel 2007 and 2010 spreadsheet files (usually encountered as .XLSX files). But while SAS Enterprise Guide can export XLS files (which are compatible with all versions of Microsoft Excel), it does not have the ability to export to a native .XLSX file.
Customers see this as a limitation. The Microsoft Excel 2007 format allows for more rows and columns than traditional XLS files can support, and SAS Enterprise Guide does not offer a point-and-click method to create output results in this new format.
In SAS 9.2, you can use PROC EXPORT or the EXCEL LIBNAME engine to write data in this new format, but using PROC EXPORT requires the customer to write a custom SAS program, and both PROC EXPORT and the EXCEL LIBNAME engine require you to license and install SAS/ACCESS to PC Files.
New Custom Task: Export Data to Microsoft Excel 2007/2010
To help customers to work around this limitation, SAS R&D has created a custom task that focuses on one thing: export your SAS data to a Microsoft Excel 2007 (.XLSX) file. Here is an example screen shot of the task:
The task supports the following features/options:
- Works only with SAS Enterprise Guide 4.3
- Export to XLSX or XLS file
- Specify the name of the worksheet in the exported file
- Specify which columns to include in the exported file
- Specify the output format (as a SAS format) for each column. This is especially useful for applying special treatment to date and date-time values to preserve the correct format in the spreadsheet.
- Optionally place field headings on the first row of the exported sheet. You can also use labels instead of variable names.
- Optionally create multiple worksheets if the row count exceeds the maximum supported by Microsoft Excel
- Optionally create multiple worksheets if the column count exceeds the maximum supported by Microsoft Excel
Note: This task uses "native" technology to do its work: SAS/ACCESS to PC Files is not required. However, this also means that the task does not generate a SAS program that can be run independently outside of SAS Enterprise Guide or within a stored process.
Download the custom task from this location (ZIP file, 473KB). Read the README PDF file in the ZIP archive to learn how to install and use it with SAS Enterprise Guide 4.3.
Update: There is also a 5.1 version of this task available via SAS Note 41132.