topic Re: ROW_HEIGHTS for TITLE statement in ODS Excel in ODS and Base Reporting

ROW_HEIGHTS for TITLE statement in ODS Excel

ColeG — Tue, 05 Jun 2018 18:12:47 GMT

I am trying to control the row heights for a title that I have added to an ods excel output. Below is sample code with only the relevant sections:

ods excel file='C:\test.xlsx';
ods excel options(sheet_name='test' absolute_column_width='11,8' row_heights='14,3' embedded_titles='yes');

title box=2 bc=CX702082 bold color=white j=left height=12pt "Test Title";

proc report data=sashelp.class; run;

What I would like to have happen is to have a title that takes up rows 1 and 2 (this is automatic from the row statement): row one would have a height in Excel of 14, row two would have a height of 3 (from the row_heights option) and basically just exist to provide a narrow buffer between my title and my report.

However, what actually happens is that rows 1 and 2 have their default height, while row 3 (the first row from the report output) has a height of 14 and all subsequent rows have a height of 3; absolute column width affects all columns starting with column A (width 11, followed by widths of 8). Why does row_heights not seem to work in the same way as absolute_column_width and is there a way to control the height of the title rows using ods excel options?

Re: ROW_HEIGHTS for TITLE statement in ODS Excel

Cynthia_sas — Tue, 05 Jun 2018 20:27:45 GMT

Hi:

The values for row_heights sub-option are positional. The 4th value is the TITLES value. When I do this:

I get a higher title. It doesn't span rows 1 and 2, but row 1 is taller than the other rows. Specifying a 0 for a "position" that I don't want to change tells ODS to use the value set by default in the current style being used for the output (in this case, HTMLBLUE). I fiddled with the number to use, 14 didn't seem big enough to be obvious, so I made it 30.

Hope this helps,

cynthia

Re: ROW_HEIGHTS for TITLE statement in ODS Excel

ColeG — Wed, 06 Jun 2018 14:24:15 GMT

Thank you so much, Cynthia! I don't think I ever would have thought to even look that up! I just assumed it would function similarly to column width. And if I set a value for position 7 I can make row 2 narrower to bring the title closer to the report.

As an unrelated follow-up: it seems like VJUST doesn't work in a TITLE statement like this; can I only get that from applying a template to the title?

Re: ROW_HEIGHTS for TITLE statement in ODS Excel

Cynthia_sas — Wed, 06 Jun 2018 15:03:49 GMT

Hi:

Yes, I think you need a template change, but it's not a hard change:

Hope this helps,

Cynthia

Re: ROW_HEIGHTS for TITLE statement in ODS Excel

ColeG — Wed, 06 Jun 2018 15:35:20 GMT

Yep, thank you so much for your help!