topic Re: Empty rows coming between excel titles using ods tag sets.excelxp in SAS Enterprise Guide

Empty rows coming between excel titles using ods tag sets.excelxp

Nimish28 — Fri, 19 Feb 2021 14:16:52 GMT

Hi All,

I am facing one issue where I am getting empty rows in between my titles while exporting using ods.

any leads on this will be appreciated.

thanks.

Re: Empty rows coming between excel titles using ods tag sets.excelxp

Cynthia_sas — Fri, 19 Feb 2021 15:01:03 GMT

Hi:

This is not what I observe when I run some test code:

With the embedded_titles='yes' suboption, I see Title1 is in row 1 and Title 2 is in row 2. I do not get an empty row between my titles. And, in PRINT PREVIEW mode in Excel, I still do not see an empty row between my titles:

You might need to work with Tech Support so they can look at your data and ALL your code and see whether there is something in your code that might be causing the empty row.

Cynthia

Re: Empty rows coming between excel titles using ods tag sets.excelxp

Nimish28 — Fri, 19 Feb 2021 15:25:14 GMT

Hi Cynthia,

Thanks for replying. I just realised that the issue is with the excel cell width due to which title text are not getting wrapped.

can you assist on fixing the cell width?

Re: Empty rows coming between excel titles using ods tag sets.excelxp

Cynthia_sas — Fri, 19 Feb 2021 19:13:17 GMT

Hi:
You'll have to figure out how you want to do it. One way is to use the H= option of the TITLE statement to make the title a smaller font:
title1 h=8pt 'Really long title';

And the other way would be to use a sub-option that tells TAGSETS.EXCELXP to use more cells for the title. By default, the title spans the number of cells in the table, so with tables that only have 2 or 3 or 5 columns, your title may end up wrapping. I believe there is a sub-option that lets you specify how many cells to use for the titles/footnotes.

If you don't know how to see the sub-options, the next time you invoke the code, add this:
ods tagsets.excelxp file='......' options(doc='Help');

The doc='Help' sub-option will put all the possible sub-options into the SAS log. I can't remember now whether it is TITLE_WIDTH or TITLE_FOOT_WIDTH or something similar. Maybe TITLE_FOOTNOTE_WIDTH. Worth investigating.

Cynthia