SAS ODS Excel - AUTOFIT_HEIGHT & VERTICALALIGN not working.

[insaynasasin]

I am trying to export SAS datasets to Excel file using ODS Excel. I want the data to be in the center vertically and Excel should be able to set the height automatically. I am using AUTOFIT_HEIGHT = "ON" & VERTICALALIGN=M options but the output Excel file has several rows where the height is not appropriate and also the data is not aligned in the center vertically. I am using the following code:

ODS ESCAPECHAR="~";
ODS EXCEL FILE = "here goes location"

OPTIONS(
SHEET_NAME = "BS"
AUTOFIT_HEIGHT = "ON"
);

PROC ODSTEXT;
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
RUN;

ODS EXCEL OPTIONS(
AUTOFILTER = "ALL"
SHEET_INTERVAL = "NONE"
AUTOFIT_HEIGHT = "ON"
);

PROC REPORT DATA=SASDATA.BS_PIVOT_2
STYLE(HEADER)=[BACKGROUNDCOLOR=BLACK COLOR=WHITE TEXTALIGN=L VERTICALALIGN=M]
STYLE(LINES)=[TEXTALIGN=L VERTICALALIGN=M];
RUN;

ODS EXCEL OPTIONS(
SHEET_NAME = "IS"
AUTOFIT_HEIGHT = "ON"
);

PROC ODSTEXT;
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
P "some text" / STYLE=[COLOR=BLACK FONT_SIZE=10PT FONT_WEIGHT=BOLD];
RUN;

ODS EXCEL OPTIONS(
AUTOFILTER = "ALL"
SHEET_INTERVAL = "NONE"
AUTOFIT_HEIGHT = "ON"
);

PROC REPORT DATA=SASDATA.IS_PIVOT_2
STYLE(HEADER)=[BACKGROUNDCOLOR=BLACK COLOR=WHITE TEXTALIGN=L VERTICALALIGN=M]
STYLE(LINES)=[TEXTALIGN=L VERTICALALIGN=M];
RUN;

ODS EXCEL CLOSE;

Please help me with this issue. This is the first time that I am asking any question on this forum. Apologies if I missed any standard information that is to be provided. Since I am using client data, I might not be able to provide a sample data. Also, I am using SAS Enterprise Guide 7.1 (64-bit).

[Norman21]

Welcome to the community!

When I run your code in SAS Enterprise Guide 8.3, the log shows the following:

WARNING: Option "autofit_height" not recognized. This option will be ignored.
NOTE: The EXCELXP option autofit_height is not supported in this Excel tagset.

I think the autofit_height option is only valid when using ExcelXP ODS tagsets. See page 16 of the following for an example:

https://www.mwsug.org/proceedings/2009/how/MWSUG-2009-H08.pdf

[insaynasasin]

Is there some other way to ask Excel to auto fit row height as well as column width and set the required vertical alignment?

[Norman21]

Not according to page 12 and 13 of this:

http://www.scsug.org/wp-content/uploads/2017/10/Chevell_SCSUG.pdf

[insaynasasin]

On page 13 for auto fit height it says - "No replacement option is necessary because the Excel destination is a measured destination." Do you mind explaining what is the meaning of this in a layman's language?

[Norman21]

I think the explanation is in the sentence on the top of page 12 "The output layout has a better appearance in the Excel destination, too, because cells are passed to a routine that measures and resizes columns for optimal display."

[jimbarbour]

For height, you might try the technique discussed here:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Row-height-too-small-after-wrapping-text-ODS-E...

Jim