Sample 43496: Convert files created using an ODS destination to native Microsoft Excel files
Various ODS destinations, such as ExcelXP, MSOffice2K, HTML, and CSV, can generate files that can be read by Microsoft Excel. Because some of the file formats that are generated from the ODS destinations create XML output, the file size can become fairly large as the number of observations grows.
The sample code on the Full Code tab, along with the attached macro, enables you to convert all of the files in a specified directory to native Microsoft Excel files.
The macro converts files based on the version of Microsoft Excel that you have installed. If you have Microsoft Excel 2007 or higher installed, the files are converted to the Open Compressed XML format (XLSX) by default. If the version of Microsoft Excel that you have installed is a version prior to 2007, the files are converted to the Excel binary format (XLS) by default. The macro uses VBScript to perform the conversion and can be modified to change the defaults. Click on the Downloads tab to download the macro.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
This sample illustrates how to convert files created using an ODS destination to native Excel files. You need to download the macro from the
Downloads tab before running this program.
/****************************************/
/* Create sample files to convert. */
/****************************************/
ods noresults;
proc sort data=sashelp.class out=test;
by age;
run;
ods tagsets.excelxp file="c:\temp\convert\temp.xml" newfile=bygroup;
proc print data=test;
by age;
run;
ods tagsets.excelxp close;
/****************************************************************************/
/* The DEFAULT= parameter points to the folder containing the .XML files. */
/* In this example, the files were generated using the ExcelXP tagset. */
/* The EXT= parameter specifies which type of file should be converted. */
/* The %INCLUDE statement includes the macro to be invoked. The macro will */
/* convert any file type that Excel can read such as HTML, XLS, CSV, XML and*/
/* and more. */
/****************************************************************************/
options noxsync noxwait;
%include "c:\convert.sas";
%convert_files(default=c:\temp\convert,ext=xml);
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
Click
here to download the macro.
This sample illustrates how to convert files created using an ODS destination to native Microsoft Excel files.
| Date Modified: | 2011-09-30 15:01:48 |
| Date Created: | 2011-06-10 14:37:17 |
Operating System and Release Information
| SAS System | Base SAS | Z64 | | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | | |
| Microsoft Windows XP 64-bit Edition | | |
| Microsoft® Windows® for x64 | | |
| Microsoft Windows 95/98 | | |
| Microsoft Windows 2000 Advanced Server | | |
| Microsoft Windows 2000 Datacenter Server | | |
| Microsoft Windows 2000 Server | | |
| Microsoft Windows 2000 Professional | | |
| Microsoft Windows NT Workstation | | |
| Microsoft Windows Server 2003 Datacenter Edition | | |
| Microsoft Windows Server 2003 Enterprise Edition | | |
| Microsoft Windows Server 2003 Standard Edition | | |
| Microsoft Windows Server 2003 for x64 | | |
| Microsoft Windows Server 2008 | | |
| Microsoft Windows Server 2008 for x64 | | |
| Microsoft Windows XP Professional | | |
| Windows 7 Enterprise 32 bit | | |
| Windows 7 Enterprise x64 | | |
| Windows 7 Home Premium 32 bit | | |
| Windows 7 Home Premium x64 | | |
| Windows 7 Professional 32 bit | | |
| Windows 7 Professional x64 | | |
| Windows 7 Ultimate 32 bit | | |
| Windows 7 Ultimate x64 | | |
| Windows Millennium Edition (Me) | | |
| Windows Vista | | |
| Windows Vista for x64 | | |
