The SAS Output Delivery System and reporting techniques

Lines Wrapping in ODS Excel

Reply
Frequent Contributor
Posts: 87

Lines Wrapping in ODS Excel

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

Grand Advisor
Posts: 9,748

Re: Lines Wrapping in ODS Excel

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

Frequent Contributor
Posts: 87

Re: Lines Wrapping in ODS Excel

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.

SAS Super FREQ
Posts: 8,647

Re: Lines Wrapping in ODS Excel

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

Frequent Contributor
Posts: 87

Re: Lines Wrapping in ODS Excel

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

Occasional Contributor
Posts: 5

Re: Lines Wrapping in ODS Excel

Hi Steve,

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

Thanks,

Jake

Occasional Contributor
Posts: 7

Re: Lines Wrapping in ODS Excel

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

Occasional Contributor
Posts: 5

Re: Lines Wrapping in ODS Excel

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.""";


linebreaks.png

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

New Contributor
Posts: 3

Re: Lines Wrapping in ODS Excel

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!)

Occasional Contributor
Posts: 7

Re: Lines Wrapping in ODS Excel

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

Learner
Posts: 1

Re: Lines Wrapping in ODS Excel

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

New Contributor
Posts: 2

Re: Lines Wrapping in ODS Excel

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.

New Contributor
Posts: 2

Re: Lines Wrapping in ODS Excel

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?

Occasional Learner
Posts: 1

Re: Lines Wrapping in ODS Excel

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

New User
Posts: 1

Re: Lines Wrapping in ODS Excel

This solution did not work for me

Post a Question
Discussion Stats
  • 20 replies
  • 6651 views
  • 9 likes
  • 14 in conversation