topic Re: Lines Wrapping in ODS Excel in ODS and Base Reporting

Lines Wrapping in ODS Excel

SteveNZ — Mon, 03 Nov 2014 19:31:59 GMT

Hi all,

I am having a look at the experimental ODS Excel in 9.4 and have it working perfectly except it keeps wrapping character variables in the output. I've tried adding column widths via the absolute_column_width under options but while it makes the column wider the text still wraps. I've also tried different styles to no avail.

Does anyone else have this problem? Has anyone solved it?

Many thanks in advance

Steve

Re: Lines Wrapping in ODS Excel

ballardw — Mon, 03 Nov 2014 20:17:06 GMT

Is there any chance your variable has a line feed or return from data entry?

Re: Lines Wrapping in ODS Excel

SteveNZ — Mon, 03 Nov 2014 20:21:16 GMT

Hiya, no I thought of that and compressed the wrapping variables but it still wraps.

I currently have an excelxp version of the report being used with the exact same data and that doesn't wrap the lines, seems specific to the ODS Excel.

Re: Lines Wrapping in ODS Excel

Cynthia_sas — Mon, 03 Nov 2014 22:14:43 GMT

Hi:

ABSOLUTE_COLUMN_WIDTH is an TAGSETS.EXCELXP sub option. I am not certain that ODS EXCEL (the experimental destination for SAS 9.4) has any sub options like that.

In this paper that discusses the new ODS EXCEL destination http://support.sas.com/resources/papers/proceedings14/SAS177-2014.pdf there is a part that says: "....for example, the Excel destination measures table cells automatically, eliminating the need to adjust the width of the cells manually with tagset options)" (page 17 of the paper)

So, if you are having issues with the new, experimental ODS EXCEL destination, my recommendation would be that you open a track with Tech Support. They can verify whether you would use ABSOLUTE_COLUMN_WIDTH with ODS EXCEL or whether it only works with TAGSETS.EXCELXP. And, if they do not know a workaround for your issue, they can help you.

cynthia

Re: Lines Wrapping in ODS Excel

SteveNZ — Mon, 03 Nov 2014 23:11:47 GMT

Hi Cynthia,

Thanks for replying. The widths option does work in the new ODS Excel as it borrows a lot of options (many deprecated) from the excelxp tagset. I did read the paper you mention and read the part about measuring table cells automatically but for whatever reason it still wraps the text.

If I set the width at say 100 then the text still wraps at about 15 which is pretty odd.

I was wondering if anyone else had come across this issue or whether it was somehow particular to my setup. I tried looking at Excel settings I could change but cant find any that solve my problem.

I'll open a ticket with Tech Support.

cheers

Steve

Re: Lines Wrapping in ODS Excel

jasu — Fri, 15 May 2015 22:53:52 GMT

Hi Steve,

I'm coming across exactly the same issue. Were you able to find a solution?

Thanks,

Jake

Re: Lines Wrapping in ODS Excel

DavidOesper — Thu, 21 May 2015 19:27:55 GMT

Hi Jake,

I'm having this problem, too. I think this is a known bug in ODS Excel. If you throw one of the "broken" cells into a hex-capable editor like UltraEdit, you will find a 0D0A (carriage return line feed) that shouldn't be there. I asked Chevell Parker about this late last fall, and he told me it was a known issue. Hopefully, this will be fixed when 9.4M3 comes out this summer. In the meantime, I sure wish there was a workaround because I've reverted back to using the EXCELXP tagset for most of my Excel output.

Dave

Re: Lines Wrapping in ODS Excel

jasu — Sun, 24 May 2015 16:04:42 GMT

Thanks for the info, Dave. I'm looking forward to the 9.4M3 release.

One potential workaround seems to be to set the WIDTH= attribute to a really high value for any columns that are being affected by the problem. For example, in the following code, there is a 300-char text variable. By default (no WIDTH= value specified), extra line breaks appear in the output. Setting WIDTH=100% reduces the number of line breaks, and WIDTH=1000% eliminates them altogether.

* Data set with a long text var ;

data foo;

length a $300;

a = repeat('long text ', 29);

run;

* Output using ODS Excel ;

%let XL_OUTFILE=C:\Users\&sysuserid\Desktop\foo.xlsx;

ods _all_ close;

ods results=off;

ods excel file="&XL_OUTFILE";

ods excel options(absolute_column_width='15' sheet_name = 'Sheet1');

ods text='Workaround for Line Breaks in Long Text';

proc report data=foo split='*';

columns a a=b a=c;

define a / 'No Width*Specification';

define b / 'Width=100%' style(column)=data[width=100%];

define c / 'Width=1000%' style(column)=data[width=1000% tagattr='wrap:yes'];

run;

ods _all_ close;

ods html;

ods results=on;

* Open the file ;

options noxwait noxsync;

x """&XL_OUTFILE.""";

I'm not sure if this will fix the problem in all scenarios, but it has been working pretty well for me so far.

Jake

Re: Lines Wrapping in ODS Excel

Jaaake — Tue, 26 May 2015 09:33:21 GMT

Thanks for that workaround Jake! I've been having the same problem, and it's worked an absolute treat.

I don't know if anyone else has had this, but in reports with these strange line breaks, it also inserted unnecessary line breaks into headers and embedded titles. I added the width style attribute for both column and header in the proc report statement, and that solved the lot.

proc report data = dataset nowindows missing split='\' spanrows style(column)=[vjust=top width=1000%] style(header)=[width=1000%];

Thanks again!

Jake (a different Jake!)

Re: Lines Wrapping in ODS Excel

DavidOesper — Tue, 09 Jun 2015 15:00:58 GMT

Thank you Jake and Jake! Please accept my apologies for not responding a couple of weeks ago. At the end of Memorial Day weekend my home and car were flooded out here in Houston and I've been pre-occupied dealing with all that. Again, thank you both for a great workaround until the problem is fixed in 9.4M3.

Best Regards,

Dave

Re: Lines Wrapping in ODS Excel

JanFF — Thu, 27 Aug 2015 08:34:13 GMT

Hi all,

I've installed SAS 9.4M3 and the wrapping error still appears. Is it possible that the problem is still not fixed? Can anyone confirm that?

Anyway, thank you Jake and Jake for the great workaround!

Best Regards

Jan

Re: Lines Wrapping in ODS Excel

anonymous_user — Fri, 30 Oct 2015 19:54:15 GMT

I'm experiencing the same problem with 9.4M2.

One work around I found successful is to use the =CLEAN() formula in Excel or Application.WorksheetFunction.Clean() in VBA. If your data contains line breaks you want to keep, you can replace those linebreaks in SAS with some arbitrary characters like BRKBRK and then use something like Replace(temp_string, "BRKBRK", Chr(10)) on the Excel side, once you CLEAN(). I have a .xlsm workbook that writes to my .xlsx file anyway, so this isn't too big of a deal for me at least.

Re: Lines Wrapping in ODS Excel

BarryParish — Sun, 08 May 2016 23:43:06 GMT

Haven't seen an update on this topic in a while. Still wrapping for me in 9.4M3.

Width=1000% still the workaround? No fix from SAS?

Re: Lines Wrapping in ODS Excel

Milo1 — Thu, 07 Jul 2016 22:02:07 GMT

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

Re: Lines Wrapping in ODS Excel

AR_BB — Mon, 10 Oct 2016 16:31:58 GMT

This solution did not work for me

Re: Lines Wrapping in ODS Excel

bartman — Wed, 03 May 2017 23:37:24 GMT

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.

Re: Lines Wrapping in ODS Excel

Chevell_sas — Wed, 10 May 2017 15:51:59 GMT

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

Re: Lines Wrapping in ODS Excel

Tom — Wed, 10 May 2017 15:54:57 GMT

@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

Re: Lines Wrapping in ODS Excel

Chevell_sas — Wed, 10 May 2017 18:24:58 GMT

@Tom

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

Re: Lines Wrapping in ODS Excel

Tom — Wed, 10 May 2017 18:31:07 GMT

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.