The ODS destination for Microsoft Excel adds carriage return/line feed characters to wrapped text within a cell


The ODS destination for Microsoft Excel uses an algorithm to determine where text in a cell should wrap for the best presentation. When the text wraps within a cell, carriage return/line feed (CRLF) characters are inserted where the wrapping occurs.

To prevent this behavior from occurring, include the FLOW="TABLES" suboption in the ODS EXCEL statement, beginning with SAS® 9.4M4 (TS1M4). Specifying this suboption prevents the ODS destination for Microsoft Excel from adding the CRLF characters for all parts of the table such as the headings, row headings, and data values. Here is sample code that illustrates the syntax. 

/* Using the FLOW="TABLES" Excel suboption */

ods excel file="c:\temp.xlsx" options(flow="tables");

proc print data=sashelp.orsales label;
run;

ods excel close;

An alternative to using the FLOW="TABLES" suboption to prevent the hard return from being added to the cells when wrapped is to use the WIDTH= style attribute with a value large enough to display all of the text without wrapping.