11-03-2014 02:31 PM
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
11-03-2014 03:21 PM
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.
11-03-2014 05:14 PM
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.
11-03-2014 06:11 PM
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.
05-21-2015 03:27 PM
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.
05-24-2015 12:04 PM
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 ;
length a $300;
a = repeat('long text ', 29);
* Output using ODS Excel ;
ods _all_ close;
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'];
ods _all_ close;
* Open the file ;
options noxwait noxsync;
I'm not sure if this will fix the problem in all scenarios, but it has been working pretty well for me so far.
05-26-2015 05:33 AM
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%];
Jake (a different Jake!)
06-09-2015 11:00 AM
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.
08-27-2015 04:34 AM
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!
10-30-2015 03:54 PM
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.