Introduction

The ODS Excel destination is popular because it generates presentation-ready output directly from your SAS^® software. This destination provides options to the Microsoft Excel worksheet and workbook that automate setting up custom features that you would otherwise manually add. This destination also includes other features such as the ability to add graphics generated by both SAS/GRAPH^® and ODS Graphics, the ability to add formulas, and the ability to add SAS formats that are directly converted to the appropriate Excel formatting. This post demonstrates various formatting issues that can occur when using ODS Excel. It also describes the following:

• handling those issues using formatting
• formatting values after you export to Excel

Getting the desired formatting for your values

The ODS Excel destination and the Microsoft Excel application use the Excel General format for all numbers that do not have an Excel format applied. In ODS Excel, you can apply Excel formats using either a SAS format, which internally converts it to the comparable Excel format, or by applying the Excel format directly using the TAGATTR= style attribute. When you do not apply an Excel format, the Excel General format is used, which makes a best guess about how the output should be formatted. Here are some issues to consider when the Excel General format is used along with other Excel or ODS Excel defaults in ODS Excel:

• Leading zeros are omitted when displaying values in the spreadsheet
• Numbers greater than 11 digits might be displayed in scientific notation
• Numbers greater than 15 digits are padded with zeros
• Characters variables that contain commas or other punctuation get stripped

Retaining the leading zero

A number with a leading zero does not get preserved when it is exported to a worksheet using ODS Excel. This behavior occurs because the defaults in the Excel General format do not display numbers with a leading zero and is true whether the value is defined as character or numeric in SAS. The absence of the leading zero matters if this is an actual account number. Output 1 demonstrates the absence of the leading zero for both acct_num and acct_char in record 1.

Output 1. Exported output from ODS Excel does not include the leading zeros

To display the leading zero for variables that are defined as numbers in SAS using SAS formatting, apply the Z. SAS format, which is demonstrated in Example 1 below for the numeric variable acct_num. Character variables that are all numbers also use the Excel General format by default and require that you apply the character format $ to preserve the leading zero. You can also add Excel formats using the TAGATTR= attribute, which is used to apply the 0 format for the numeric field. This attribute uses the TYPE:STRING parameter in the TAGATTR= style attribute, which modifies the data type to a string that retains the leading zero in Excel. Output 2 shows the formatting that results from running the code in Example 1.

Example 1

data example_1;
input acct_num acct_char $;
cards;
0912556 0912556 
2330202 2330202 
;
run;
 
 
/* Using SAS formatting to preserve the leading zero. */
 
ods excel;
proc print data=example_1;
format acct_num z7.;
format acct_char $7.;
run;
ods excel close;
 
/* Using the TAGATTR= style attribute to preserve the leading zero. */
 
ods excel;
proc print data=example_1;
var acct_num /   style(data)={tagattr="format:0000000"};
var acct_char /  style(data)={tagattr="type:String"};
run;
ods excel close;

Output 2. The leading zero is retained

Preventing formatting with scientific notation

Exporting numbers using ODS Excel with values greater than 11 digits might display cells using scientific notation in the worksheet. This issue occurs regardless of how this number was defined in SAS unless an Excel format has been applied. Output 3 illustrates the issue.

Output 3. Outputs long numbers using scientific notation

To correct the formatting, add a SAS format such as the BEST. format for the numeric variable acct_num and the character format $ for the character variable acct_char. You can also apply a custom Excel format directly using a custom number format for the numeric variable and the TYPE:STRING parameter within the TAGATTR= attribute, which forces the type to be a string in Excel. Output 4 shows the formatting that results from running the code in Example 2.

Example 2.

data sample_2;
 input acct_num acct_char $12.;
 cards;
 123456789012 123456789012 
 456789012345 456789012345 
 ;
 run;
 
 
/* Using SAS formatting to display the full value. */
 
 ods excel file="c:\output\temp.xlsx";
 
 proc print data=sample_2;
 format acct_num best12.;
 format acct_char $12.;
 run;
 
 ods excel close;
 
/* Using the TAGATTR= style attribute to display the full value. */
 
 ods excel file="c:\output\temp.xlsx";
 
 proc print data=sample_2;
 var acct_num  / style(data)={tagattr="format:#####"};
 var acct_char / style(data)={tagattr="type:String"}; 
 run;
 
 ods excel close;

Output 4. Displays the full value

Excel pads values with zeros

Excel can store a number only up to 15 significant digits. After the 15^th digit, Excel begins to pad the value with zeros, as shown in Output 5.

Output 5. Values are incompletely displayed

If you have a meaningful number that is greater than 15 digits, you must make sure that all digits are displayed. Since Excel cannot display more than 15 digits, this number must be treated as a string, which can be accomplished by using the TYPE:STRING parameter within the TAGATTR= attribute. However, you first must use the BEST. format to obtain all of the digits before storing this value as a string. Output 6 illustrates the formatting that you get after running the code from Example 3.

Example 3.

data sample_3;
 input acct_num acct_char $16.;
 cards;
 1234567890123456 1234567890123456 
 4567890123456789 4567890123456789 
 ;
 run;
 
 ods excel file="c:\output\temp.xlsx";
 
 proc print data=sample_3;
 var acct_num / style(data)={tagattr="type:String"};
 var acct_char; 
 format acct_num best16.;
 format acct_char $16.;
 
 run;
 
 ods excel close;

Output 6. The complete value is displayed

Character variables are stripped of punctuation

If a number contains punctuation such as a comma, it is stripped when it is exported to Excel using ODS Excel, as shown in Output 7.

Output 7. Output from the spreadsheet

If the expected behavior is to format the number with the punctuation in Excel, you can format it using both SAS formatting and Excel formatting. Example 4 uses the COMMA SAS format to format the value with commas. This example uses the character format $ to maintain the embedded punctuation within the character variable. Excel formatting is also applied to add the comma to both variables using the TAGATTR= style override. Output 8 shows the formatting that results from running the code in Example 4.

Example 4.

data example_4;
input acct_num comma5. +1 acct_char $5.;
cards;
3,000 4,000 
1,000 6,000 
;
run;
 
/* Using SAS Formatting to add or retain punctuation. */
 
ods excel;
 
proc print data=example_4;
format acct_num comma5.; 
format acct_char $5.;
var acct_num ;
var acct_char;
run;
 
ods excel close; 
/* Using the TAGATTR= style attribute to display the punctuation. */
ods excel;
proc print data=example_4;
var acct_num / style(data)={tagattr="format:#,###"};
var acct_char / style(data)={tagattr="format:#,###"};
run;
ods excel close;

Output 8. Punctuation is added or retained

Conclusion

This blog highlighted some of the potential formatting issues that you could encounter when exporting to Excel using the ODS Excel destination. ODS Excel does an amazing job at limiting some of these issues that exist with Excel in general. Hopefully the tips provided here will help you if you encounter any of these issues.