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).
