topic Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping? in SAS Programming

ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

derekg — Wed, 23 Nov 2022 00:21:13 GMT

If I run the code below, the second string displays in the resulting file with the "Q" below the A thru P, though there appears to be sufficient space for the Q to appear directly to the right of the P. I can solve this by adding options(flow="tables") to the ods excel statement, which stops the Q from wrapping onto the next line. The issue is that, when I do this, the height of the cell containing the second string is still greater than the height of the cell containing the first string. Is there a way for the height of the taller row to be reduced so that it matches the height of the shorter row (other than manually changing it within Excel)? I tried cellheight but that didn't seem to work.

Thanks!

ods excel file = "[filepath].xlsx";

data d;
	length x $ 100;
	x = "A B C D E"; output;
	x = "A B C D E F G H I J K L M N O P Q"; output;
run;

proc report data = d;
	column x;
	define x / style(column) = [cellwidth = 2 in] display;
run;

ods excel close;

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

Ksharp — Wed, 23 Nov 2022 02:07:17 GMT

ods excel file = "c:\temp\temp.xlsx";

data d;
	length x $ 100;
	x = "A B C D E"; output;
	x = "A B C D E F G H I J K L M N O P Q"; output;
run;

proc report data = d nowd;
	column x;
	define x / style(column) = [tagattr='wrap:no'] display;
run;

ods excel close;

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

Ksharp — Wed, 23 Nov 2022 02:10:15 GMT

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

derekg — Wed, 23 Nov 2022 14:49:57 GMT

I had tried playing around with tagattr='wrap:no' as well... Unfortunately, I don't think this solution quite accomplishes what I'm looking for. It does fix the cell height issue, but it does so by increasing the cell width (beyond the width of 2 inches I had used in my example). I probably should have been clearer about this, but I'm looking for a solution that would not require increasing the cell width (or changing the size of the text).

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

ghosh — Wed, 23 Nov 2022 16:51:21 GMT

Just add OPTIONS(FLOW='Tables') to the ods excel file line

ods excel file = '~/test/wrap.xlsx'  OPTIONS(FLOW='Tables');

data d;
	length x $ 100;
	x = "A B C D E"; output;
	x = "A B C D E F G H I J K L M N O P Q"; output;
run;

proc report data = d;
	column x;
	define x /  display;
run;

ods excel close;

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

derekg — Wed, 23 Nov 2022 19:20:51 GMT

Again, similarly to Ksharp's answer, in this block of code, the "cellwidth = 2 in" that I used in my original example has been removed. So, at least when I run this code, this results in a cell width that is slightly more than 2 inches. I am trying to find a solution that would allow the cell width to remain fixed at 2 inches, while keeping the height of rows 2 and 3 equally short.

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

ghosh — Wed, 23 Nov 2022 20:34:01 GMT

To fit within 2 in you may need to use a smaller font, otherwise it would wrap with the default font

ods excel file = '~/test/wrap.xlsx'  
   OPTIONS(FLOW='Tables' absolute_column_width='2.1in');

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

derekg — Wed, 23 Nov 2022 21:47:27 GMT

It seems that absolute_column_width may work differently from cellwidth. If I set a width of 2 inches using absolute_column_width, I get a shorter width than if I set a width of 2 inches using cellwidth. Interestingly, though, if I set a width of 2.2 inches using absolute_column_width, then this comes close to providing a solution to my problem, as the resulting file has the two rows printed without wrapping, with equal row heights, and without a greater width than the width used from my original example (i.e., 2.2 inches using absolute_column_width actually provides a shorter width than 2 inches using cellwidth).

However, my understanding is that absolute_column_width would set the length of all columns in the table, right? Ultimately, the application for this would involve a table with multiple columns, where I will want it to be possible for different columns to have different widths.

For example, if I have something like this, where I want columns x and y to have different widths, I'm back to having the same problem where row 3 has unnecessary extra vertical height.

ods excel file = "[filepath].xlsx"  OPTIONS(FLOW='Tables');

data d;
	length x y $ 100;
	x = "A B C D E"; y = "A B C D E"; output;
	x = "A B C D E F G H I J K L M N O P Q"; y = "A B C D E F G H I J K L M N O P Q"; output;
run;

proc report data = d;
	column x y;
	define x / style(column) = [cellwidth = 3 in] display;
	define y / style(column) = [cellwidth = 2 in] display;
run;

ods excel close;

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

derekg — Wed, 23 Nov 2022 21:50:12 GMT

Oh, wait a minute... I see that you can set multiple widths using absolute_column_width. Maybe that will work...

Re: ODS Excel / PROC REPORT - How to remove extra row height when preventing text wrapping?

ghosh — Wed, 23 Nov 2022 21:52:04 GMT

Exactly, you can list the cell widths in a comma delimited string