Usage Note 68438: How to format the cells of a worksheet that is using the ODS Excel destination
 |  |  |  |
The ODS Excel destination enables you to add cell formatting within a worksheet by using SAS® formatting. If a SAS format is applied, the corresponding Excel format is generated and carried forward in Excel. If no SAS format is applied, the general Excel format is applied to cells, which may generate a problem with the display, such as a loss of a leading zero for the cell.
Example 1 shows how you can apply the SAS Z. format to a variable and prevent the loss of a leading zero for a field in Excel.
/* Example 1. Using SAS formatting */
data one;
var1=00001;
run;
var1=00001;
run;
ods excel file="c:\temp\sample.xlsx";
proc print data=one;
format var1 z5.;
run;
format var1 z5.;
run;
ods excel close;
The ODS Excel destination also enables you to add Excel formatting directly by using the TAGATTR= style attribute. Example 2 demonstrates how to use the TAGATTR= attribute to modify the data type to String, which maintains the leading zero.
/* Example 2. Using the TAGATTR= style override */
data one;
var1="00001";
run;
var1="00001";
run;
ods excel file="c:\output\temp.xlsx";
proc print data=one;
var var1 / style(data)={tagattr="type:String"};
run;
var var1 / style(data)={tagattr="type:String"};
run;
ods excel close;
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4_M3 | 9.4 TS1M3 | ||
| z/OS 64-bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft® Windows® for x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8 Enterprise x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8 Pro 32-bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8 Pro x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 8.1 Pro x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows 10 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2008 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2008 R2 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2012 R2 Std | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2012 Std | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Enterprise 32 bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Enterprise x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Home Premium 32 bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Home Premium x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Professional 32 bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Professional x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Ultimate 32 bit | 9.4_M3 | 9.4 TS1M3 | ||||
| Windows 7 Ultimate x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| 64-bit Enabled AIX | 9.4_M3 | 9.4 TS1M3 | ||||
| 64-bit Enabled Solaris | 9.4_M3 | 9.4 TS1M3 | ||||
| HP-UX IPF | 9.4_M3 | 9.4 TS1M3 | ||||
| Linux for x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Solaris for x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Type: | Usage Note |
| Priority: |
| Date Modified: | 2021-10-08 14:18:28 |
| Date Created: | 2021-09-27 08:37:27 |