Lines Wrapping in ODS Excel

[MarcoGuimaraes]

Thanks, Jadu! The attribute "width=100%" worked fine with ABSOLUTE_COLUMN_WIDTH .

[Milo1]

I changed my stylesheet from BarrettsBlue to Sasweb and the wrapping went away. Might be worth a try to check other styles.

[AR_BB]

This solution did not work for me

[bartman]

I was looking for the same information and wonder if this fixes it:

"Text wrapping is handled differently in the Excel destination. The TAGATTR="WRAP:"YES | NO" attribute is included in the Excel destination to handle the same functionality as the WRAPTEXT="YES | NO" tagset option."

from A Ringside Seat: The ODS Excel Destination versus the ODS ExcelXP Tagset pg 14.

That didn't seem to change things.

But looking on pg 17 it shows using the TAGATTR setting on individual columns of a proc print var. I'm going to try that and get back here.

[Chevell_sas · Accepted solution]

The FLOW= sub-option was added with SAS 9.4M4 which prevents the hard return from being added to column forcing the wrap. Therefore, you can then specify the desired width using for instance the ABSOLUTE_COLUMN_WIDTH= and this will wrap based on the width specified for the column. Specifying the FLOW='Tables'  for example will prevent the hard return from being added to any of the columns within the table.  See page 2 in the below document for more information on the option.

http://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf

[Tom]

@Chevell_sas

That paper mentions inserting CRLF. Does it really insert both a CR and a LF character?  Or does it just insert just the CR character which is more common in multi line cells in Excel?  If so it could have a big impact on the usability of any CSV files generated from the Excel file.

---

@Chevell_sas wrote:

The FLOW= sub-option was added with SAS 9.4M4 which prevents the hard return from being added to column forcing the wrap. Therefore, you can then specify the desired width using for instance the ABSOLUTE_COLUMN_WIDTH= and this will wrap based on the width specified for the column. Specifying the FLOW='Tables'  for example will prevent the hard return from being added to any of the columns within the table.  See page 2 in the below document for more information on the option.

http://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf

---

[Chevell_sas]

@Tom

It does actually insert both CR and LF when this wrap occurs.

[Tom]

So can we make a change request to have NOT do that in the future?  

There are literally hundreds of posts on the various SAS discussion boards about how to fix delimited files that have been corrupted in exactly that way by having end-of-line characters embedded in character variables.  The easiest solution is when the embedded line break is just a CR and the file is either delimited with CRLF (like on Windows/DOS) or LF (like on Unix).

data want ;
  infile 'mybadfile.csv' dsd firstobs=2 termstr=crlf ;
  length var1 - var20 $200;
  input var1-var20;
run;

When the literal end-of-line characters are embedded into a field it can be impossible or really hard to fix the file so that it can be read.

[Chevell_sas]

The behavior was addressed with the M4 by adding the new FLOW= option which allows you to modify this behavior for individual parts of the table , the entire table,  the text or a specified range. So there is a way to prevent this default,  although the default has not changed.   

[bartman]

Yes, on this Tips page, it states "You can also use the Excel destination's new FLOW= suboption, which is available in the fourth maintenance release for SAS 9.4 (TS1M4). This option contains the parameters TABLES, ROWHEADERS, DATA, TEXT, and a range (for example, A1:E20). When you specify the parameter TABLES, that automatically includes the HEADERS, ROWHEADERS, and DATA parameters."

http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Search for the word flow on the page - nice addition.

[zazu]

I've tried what Chevvel suggested and it didn't work BUT it turns out that if your table is bigger than a certain size as per SAS default you need to specify:

options papersize=(22in 27in);

  <rest of your ods code >

or whatever size it is that  you need!

hope it works for you

[Jay_TxOAG]

I have tested this on 9.4M4 and 9.4M6

We use Proc Report in our Stored Process reports with links to Excel. Using AUTOMATIC_COLUMN_WIDTH in the ODS EXCEL options and CELLWIDTH as a STYLE option on the DEFINE statement in PROC REPORT, we get some control over our EXCEL cells.

ODS EXCEL File="C:\COLUMN WIDTH TEST.xlsx";

ODS EXCEL Options(sheet_name="DESC COLUMN BREAKS"
absolute_column_width= '50,50,50');
proc report data = sashelp.moniker nowd;
Column Client Server Desc;
Define Client / "Client";
Define Server / "Server";
Define Desc / "Desc" ;
run ;

ODS EXCEL Options(sheet_name="DESC COLUMN NO BREAKS"
absolute_column_width= '50,50,50');
proc report data = sashelp.moniker nowd;
Column Client Server Desc;
Define Client / "Client";
Define Server / "Server";
Define Desc / "Desc" style(column) = {cellwidth=3.75in};
run ;