Row height too small after wrapping text (ODS EXCEL)

[ColeG]

Sorry if this has been posted before, my searching all led to people with the opposite problem. I am outputting a formatted report for use by field staff. It needs to be editable in Excel (checking off rows, deleting rows, etc. as they need to), but also needs to fit on one page width for printing to take to field sites. Since width is a priority I am using text wrapping but for some reason SAS/Excel refuses to make the rows tall enough to display all the text, although row height is greater than in just a blank file so clearly SOMETHING is working. Every row is 34.50 Excel units (46 pixels) in height, but the needed height varies between 15.00 and 45.00 Excel units because every cell has 1-3 lines. I have no idea how the current value happened.

Below is a sample showing the options I am using:

ods excel file="output.xlsx" style=mystyle;
ods excel options (absolute_column_width='15.14,12.14,11.86,16.57,49.71,17.71' embedded_titles='no' flow='tables');
proc report data=mydata headskip spanrows nowd split='*'
	style(header)={just=center vjust=middle} 
	style(column)={just=left}
	style(lines)={just=left};
 columns variables;
 define old_item / display noprint;
 define variables / display 'label' style(column)={tagattr='wraptext:yes'};
 compute old_item;
 	if old_item=1 then call define(_row_,"style","style={background=very light yellow}");
 endcomp;
run;
ods excel close;

I am using a custom style, but that shouldn't be the issue. I have numeric data formatted to look like text, which might be the issue. I have the flow option set which helped but didn't solve the issue. I also have a hidden computed column that highlights certain rows which I can't imagine is having an affect on the row heights. Overall it's a pretty minimally formatted file but I can't find any options I'm missing.

Things I have tried that didn't work: ROW_HEIGHT, I don't want all my rows to be the same height, I want them to be tall enough; turning off flow; setting cell WIDTH within PROC REPORT STYLE options; AUTOFIT_HEIGHT, which was an unrecognized option that I saw written about; and PAGE_FITHEIGHT, another unrecognized option I saw written about.

Thanks for your help!

[Cynthia_sas]

Hi:
Without data, nobody can run your code. Do you have some test data to use? Otherwise, you're asking people to guess what your data looks like.

And, your sample code does not make sense to me. I don't know why you don't have old_item on the column statement. You should be seeing this message in the LOG:
WARNING: old_item is not in the report definition.

I find it easier to take a step back before I try absolute column width suboption to try the simple width= adjustment approach in a style override. For example, I could get changes in height by just changing width in a style override, as shown below.

  I find that Excel always changes whatever numbers I send it for width, so I just send in inches using width= and height typically adjusts accordingly without needing wrap text. Using much simpler code and some fake data, I get this:

Generally, I recommend not trying to over-control height and width.

Cynthia

[ColeG]

Thank you; it worked to define the width for each variable, but I had to keep absolute_column_width. As I mentioned, because I need it to fit on one piece of paper, space is an issue (columns need to be wide enough for the text within, but narrow enough to all still fit on one page). I find that absolute_column_width works better than style(column) width= in Excel, which is why I tend not to use it, because of how Excel defines column width and so with absolute width you can force it to exactly what you need it to be.

I realize that my code would not run but since I just wanted to show the options I was using and to ask what other options there might be that I don't know about I figured it was okay. I included things like the hidden column old_item just in case for some reason hidden columns at the beginning of the report will affect row height in a way I may not be aware of.

[Cynthia_sas]

Hi:
NOPRINT items do not impact row height. Row height is determined by a combination of width adjustments, height adjustments, font size, the "fit" attributes and other options that all interact.

I'd suggest starting simple then, with your absolute column width only, see where the print area would be with just that, then add the fit to page. You might be able to shrink the font size by a point or two like maybe 10pt instead of 11pt to see if that makes things fit better.

I find Excel very touchy and if I have too many option specified in ODS, they don't always seem to be implemented in Excel to give me the desired results.

If you have something that works for you, then that's good.
Cynthia

[RichardDeVen]

When you do not have flow=, the ODS EXCEL destination will 'wrap' text by adding embedded newlines in the cell value. Not always the best result. I find that the destinations internal 'measuring' algorithm can be pretty lousy for cells with longish texts.