Several ODS destinations are commonly used for exporting output to Microsoft Excel. Some of the common destinations include Tagsets.ExcelXP, MSOffice2K, HTML/HTML3, and the CSV destination. All of these ODS destinations create output that can be read by Excel. However, none of these destinations generate native (binary) Excel files. The ExcelXP destination generates XML, the MSOffice2K (like the HTML/HTML3 destinations) generates HTML, and the CSV destination generates a comma-separated file. Because the output of all of these file formats is text, the file size can grow quickly depending on the amount of variables and observations in the data set.

Methods of reducing the file size include:

• Use the ODS Excel destination which generates native Excel files.

• Manually opening the file created with ODS and re-saving the file as a native Excel file or programmatically resaving the file with information from SAS Note 43496.

• Creating a CSV file using the CSV destination.

• Programmatically modifying the output type of the file using DDE if on windows. Below is an example of programmatically saving the file to a native Excel file.

     ods tagsets.excelxp file="c:\temp\temp.xml";
     proc print data=sashelp.class;
     run;
     ods tagsets.excelxp close;
  
     options noxwait noxsync;
     filename cmds dde 'excel|system';
     data _null_;
        file cmds;
        X=SLEEP(10);
        put "[open(""C:\temp\temp.xml"")]";
        put '[ERROR("FALSE")]';
        put "[SAVE.AS(""C:\temp\temp.xls"",1)]";
        X=SLEEP(2);
        put '[close("false")]';
     run;
  

Operating System and Release Information

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	Base SAS	64-bit Enabled Solaris	9.1 TS1M3 SP4
		HP-UX IPF	9.1 TS1M3 SP4
		64-bit Enabled AIX	9.1 TS1M3 SP4
		64-bit Enabled HP-UX	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Windows Vista	9.1 TS1M3 SP4
		Microsoft Windows Server 2008	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3 SP4
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3 SP4
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3 SP4
		z/OS	9.1 TS1M3 SP4
		Solaris for x64	9.1 TS1M3 SP4
		Linux	9.1 TS1M3 SP4
		OpenVMS Alpha	9.1 TS1M3 SP4
		Linux on Itanium	9.1 TS1M3 SP4
		Tru64 UNIX	9.1 TS1M3 SP4

* 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.

---

Some files generated by ODS for use with Microsoft Excel are very large. This note contains information and sample code to reduce the file size.

Type:	Usage Note
Priority:
Topic:	SAS Reference ==> ODS (Output Delivery System) Third Party ==> Output ==> XML Data Management ==> Data Sources ==> External Databases ==> MS Excel

Date Modified:	2010-02-03 11:09:42
Date Created:	2009-08-31 15:49:56