If you are using SAS 9.4M4 or later, you can use the FLOW= suboption.
@Chevell_sas wrote a blog post that mentions the text wrapping issue. See Tips for Using the ODS Excel Destination for more details.
Example code:
%let folder=/folders/myfolders/ODS Excel examples;
* some of the player names in SASHELP.BASEBALL are long. in example
* below, some player names will wrap in Excel output. if you disable
* wrap text in Excel, you will notice that some spaces are lost. player
* names are Lastname, Firstname. the names that were wrapped lose the
* space between the comma and the Firstname. ;
ods excel file="&folder/long_text.xlsx"
options(sheet_name='Using defaults'
index='on');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
* flow= option is new for SAS 9.4M4. for more details, see
* https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/ ;
ods excel options(flow='tables'
sheet_name='Using flow option');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
* ok, long names no longer wrap, but now the rows where the names had
* wrapped are bigger than the other rows. use row_height= option.
* there are 7 parameters for row_height. 0 means use default from
* style. ;
ods excel options(flow='tables'
row_heights='0,14,0,0,0,0,0'
sheet_name='Using flow and row_height');
proc report data=sashelp.baseball;
column name team league division natbat nhits nruns nrbi;
run;
ods excel close;
The screen shot below shows the player's name column with text wrapping.
Screen shot showing long text wrapping in ODS Excel output
If you disable text wrapping in Excel, you may notice that you lose some blank spaces in the output. Player names are listed as Lastname, Firstname. The text that had wrapped loses the space between the comma and the first name. The screen shot below shows the name that had wrapped after I disabled text wrapping in Excel.
Screen shot showing long text after disabling text wrapping in Excel
The flow suboption prevents the long names from wrapping, but now the row height for long names appears to be twice the row height of the other rows.
Flow suboption prevents text wrapping, but some rows are twice as tall as other rows
Using the row_height suboption with the flow suboption produces nice output.
Screen shot showing long text not wrapping, all rows in body of report have same row height
... View more
