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;
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;
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;
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');
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.