BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SteveNZ
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Chevell_sas
SAS Employee

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

View solution in original post

26 REPLIES 26
ballardw
Super User

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

SteveNZ
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

SteveNZ
Obsidian | Level 7

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

jasu
Fluorite | Level 6

Hi Steve,

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

Thanks,

Jake

DavidOesper
Fluorite | Level 6

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

jasu
Fluorite | Level 6

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

Jaaake
Fluorite | Level 6

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

DavidOesper
Fluorite | Level 6

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

JanFF
Calcite | Level 5

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

snorehorse
Calcite | Level 5

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.

BarryParish
Calcite | Level 5

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?

getright
Calcite | Level 5

if you have SAS 9.4 M4 or higher, you can use FLOW='Tables' to fix this

MarcoGuimaraes
Obsidian | Level 7

Hey!!!

 

That's what worked for me: tagattr='wrap:yes'. But the final result was perfect with the addition of width=1000%.

I've tried all the other stuff cited here, but even with a column larger than the text, SAS was keeping the line broken/wrapped (only for the lines with the longer values in the column).

 

Now, it's "1000%" Ok. 

 

Thanks for your time here, @jasu (your post was a long time ago, but it's still useful).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats