topic Re: How do I make the second row full height, so that all the text appears in the box? in ODS and Base Reporting

How do I make the second row full height, so that all the text appears in the box?

cuevasj — Tue, 12 Nov 2019 18:57:38 GMT

I have grouped headers, and I need the header lines on line two (2) to show completely. See attached excel file to see what I am talking about. when I use row_heights='0,0,0,40', both rows 1 and 2 are enlarged rather than just row 2.

data coqui;
set sashelp.cars;
spacer=' ';
run;

ods excel file="\\t002pmid03\sasdata\cuevas\example_header.xlsx" <<< change to a location that will work for you
style=plateau
options(frozen_headers='2'
autofilter='1-4'
orientation='landscape'
row_repeat='1-2'
embed_footnotes_once='On'
absolute_column_width='16,16,36,5,5,5,5,5,5,5,5,5,5,5,5,5,5,1,5,5,5,5,5,5,5,5,5,5,5,5'
sheet_name='ES PMA 1 - Projection');
proc report data=coqui misssing nowd split='~' spanrows
style(header)={font_face=Calibri font_size=13pt just=c vjust=b tagattr='wraptext:no' background=white font_weight=bold borderwidth=2 bordercolor=black}
style(column)={font_face=Calibri font_size=10pt just=c color=BLACK tagattr='wraptext:no' background=white borderwidth=1 bordercolor=black} ;
columns (
('SAS Help Cars Data'
make model type origin driveTrain)

('SAS Help Cars Data Details'
MSRP invoice engineSize cylinders horsepower MPG_city MPG_highway)
(' ' spacer )
('SAS Help Cars: More data'
weight wheelbase length )
);
define make / display style(column)={just=l tagattr='wraptext:no' width=100%} 'Region Sup';
define model / display style(column)={just=l tagattr='wraptext:no' width=100%} 'Exec Dir';
define type / display style(column)={just=l tagattr='wraptext:no' width=100%} 'School';
define origin / display style(column)={just=c} style(header)={vjust=m background=#DDEBF7 tagattr='Format:@ rotate:90 wraptext:no' width=100%}'Title 1';
define driveTrain / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 ELA Ach';
define MSRP / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 ELA Gain';
define invoice / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 ELA BQ';
define engineSize / display style(column)={just=c} style(header)={background=#ADDE7D tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 Math Ach';
define cylinders / display style(column)={just=c} style(header)={background=#ADDE7D tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 Math Gain';
define horsepower / display style(column)={just=c} style(header)={background=#ADDE7D tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 Math BQ';
define MPG_city / display style(column)={just=c} style(header)={background=#9BF0F4 tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 Science';
define MPG_highway / display style(column)={just=c} style(header)={background=#facff3 tagattr='Format:@ rotate:90 wraptext:no' width=100%}'PMA 1 Total Points Earned';

define spacer / display style(column)={background=white} style(header)={background=white tagattr='Format:@ rotate:90 wraptext:no' width=100%}' ';

define weight / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'2018-19 ELA Ach';
define wheelbase / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'2018-19 ELA Gain';
define length / display style(column)={just=c} style(header)={background=#C2A2EB tagattr='Format:@ rotate:90 wraptext:no' width=100%}'2018-19 ELA BQ';

run;

Re: How do I make the second row full height, so that all the text appears in the box?

ChrisNZ — Wed, 13 Nov 2019 03:23:13 GMT

Did you see this paper?

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2479-2018.pdf

Re: How do I make the second row full height, so that all the text appears in the box?

cuevasj — Wed, 13 Nov 2019 13:01:39 GMT

Yes, thank you ChrisNZ. However the only thing I could see that addressed my issue is perhaps using absolute_row_height, but in order to use this, I would need to know, before the proc report executed, how many rows were produced so I could do something like this:

absolute_row_height='20,180,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0'

There just has to be a better way.

Re: How do I make the second row full height, so that all the text appears in the box?

ChrisNZ — Thu, 14 Nov 2019 05:43:14 GMT

What if you only specify the first rows' height?

Re: How do I make the second row full height, so that all the text appears in the box?

cuevasj — Thu, 14 Nov 2019 20:24:34 GMT

absolute_row_height works like the format painter in excel. If you set the first row to say 20, then all rows will be 20. if you set the first row to 20, then the second row to 30, then every other row will be 20,30,20,30,20,30, etc. In order to get excel to use the standard height, you use zero. So if you want to set the height of the two top rows, but row three through the eof can all be regular/auto, then you would have to know the number of rows in order to put in enough zeros to cover all rows or the 20,30 itteriation will repeat once all the zero rows are exhausted.
I know I could do a vlookup or data step or proc sql to get the number or rows, then set up an array or loop to print out a string that has as many zeros as rows, but that seems like an overkill on program to do something that should be pretty straight forward.

Re: How do I make the second row full height, so that all the text appears in the box?

ChrisNZ — Thu, 14 Nov 2019 23:30:18 GMT

> absolute_row_height works like the format painter in excel.

I see. It looks like the solution is the unwieldy long list of zeros then.

> that seems like an overkill on program to do something that should be pretty straight forward.

This looks like an Excel limitation (feature even, and I am no fan of Excel) more than SAS one.

Since you are showing several variables side by side, you can't define the height when defining the variable.

So using the Excel built-in formatting capability seems logical to me.

How would you like the SAS syntax to be?

Re: How do I make the second row full height, so that all the text appears in the box?

Cynthia_sas — Fri, 15 Nov 2019 00:18:35 GMT

Hi:

I see two possible solutions.

1) If you want to stick with vertical headers, you can make BOTH of the first header rows tall enough for the text and then after the file is opened in Excel, only the top row has to change. (Personally, I do not recommend because screen reader programs for visually impaired will frequently have issues with vertically oriented headers.) that will look like THIS:

This leaves you with both of the header rows the specified height (2"), but that is better than using the absolute row heights approach. At least this leaves you with just 1 row to adjust. On the other hand, my understanding most screen reader programs for the visually impaired will spell out the header starting with the top letter in the cell, not the bottom letter in the cell so your vertical headers will be unintelligible to the person using a reading program.

2) Abandon the rotated Vertical Headers and let SAS make the second row as tall as it needs to be. Will look like THIS:

Here's the revised code for #2:

data coqui;
set sashelp.cars(obs=15);
spacer=' ';
run;

ods excel file="c:\temp\examp_hdr_alt.xlsx"  
    style=plateau
	options(orientation='landscape'
            embed_footnotes_once='On'
            absolute_column_width='15,15,30,7,7,7,7,7,7,7,7,7,1,8,8,8'
            sheet_name='ES PMA 1 - Projection');
proc report data=coqui misssing nowd split='~' spanrows
style(header)={font_face=Calibri font_size=13pt just=c vjust=b 
               background=white font_weight=bold borderwidth=2 bordercolor=black}
style(column)={font_face=Calibri font_size=10pt just=c color=BLACK 
               tagattr='wraptext:no' background=white borderwidth=1 bordercolor=black} ;
columns (('SAS Help Cars Data'make model type origin driveTrain)
         ('SAS Help Cars Data Details'
           MSRP invoice engineSize cylinders horsepower MPG_city MPG_highway)
         (' ' spacer )
         ('SAS Help Cars: More data'weight wheelbase length ));

define make / display style(column)={just=l  } 'Region Sup';
define model / display style(column)={just=l  } 'Exec Dir';
define type / display style(column)={just=l  } 'School';
define origin / display style(column)={just=c} 
       style(header)={vjust=m background=#DDEBF7 tagattr='Format:@' }'Title 1';
define driveTrain / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'PMA 1 ELA Ach';
define MSRP / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'PMA 1 ELA Gain';
define invoice / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'PMA 1 ELA BQ';
define engineSize / display style(column)={just=c} 
       style(header)={background=#ADDE7D tagattr='Format:@' }'PMA 1 Math Ach';
define cylinders / display style(column)={just=c} 
       style(header)={background=#ADDE7D tagattr='Format:@' }'PMA 1 Math Gain';
define horsepower / display style(column)={just=c} 
       style(header)={background=#ADDE7D tagattr='Format:@' }'PMA 1 Math BQ';
define MPG_city / display style(column)={just=c} 
       style(header)={background=#9BF0F4 tagattr='Format:@' }'PMA 1 Science';
define MPG_highway / display style(column)={just=c} 
       style(header)={background=#facff3 tagattr='Format:@' }'PMA 1 Total Points Earned';
define spacer / display ' ' style(column)={background=white} 
       style(header)={background=white foreground=white};
define weight / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'2018-19 ELA Ach';
define wheelbase / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'2018-19 ELA Gain';
define length / display style(column)={just=c} 
       style(header)={background=#C2A2EB tagattr='Format:@' }'2018-19 ELA BQ';
run;
ods excel close;

Hope this helps,

Cynthia

Re: How do I make the second row full height, so that all the text appears in the box?

cuevasj — Mon, 18 Nov 2019 13:04:37 GMT

perhaps a backslash and then a "norepeat" ?

Absolute_Row_Height='15,30,12' \ norepeat

Re: How do I make the second row full height, so that all the text appears in the box?

Cynthia_sas — Mon, 18 Nov 2019 15:51:44 GMT

Hi:

When I look in the documentation https://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en for ODS EXCEL, I do NOT see a \norepeat option. The doc says that the absolute_row_height is used for EACH row:

I don't think you can make it work the way you envision.

Cynthia

Re: How do I make the second row full height, so that all the text appears in the box?

cuevasj — Mon, 18 Nov 2019 17:51:34 GMT

ChrisNZ was asking me IF it were an option, how would I envision that it would work, so that was my response to him.
I am going to try the route of a data step (or two) that counts rows, then loops thorough and gives me a string of comma separated zeros.
Thanks to you both for responding.

Re: How do I make the second row full height, so that all the text appears in the box?

Cynthia_sas — Mon, 18 Nov 2019 19:31:04 GMT

Hi:
Before you go down that road, I'd encourage you to open a track with Tech Support to find out what the limit is for a suboption string like you envision you will need. I suspect that for a smallish sheet, the absolute_row_height with a bunch of zeroes would be OK. But I worry that there might be a limit on the size of that string you pass. Tech Support would be able to investigate and verify before you take this approach.
Cynthia

Re: How do I make the second row full height, so that all the text appears in the box?

ChrisNZ — Mon, 18 Nov 2019 21:03:19 GMT

> and then a "norepeat" ?

This could work if the no-repeat feature is supported my Excel, which you said it is not.

Otherwise, asking SAS to have more features than Excel to create Excel formatting, while of course useful, is a very slippery slope.