topic Re: ODS Excel PROC REPORT TITLE Formatting in SAS Programming

ODS Excel PROC REPORT TITLE Formatting

Tina1202 — Tue, 15 May 2018 19:23:30 GMT

I'm using a PROC REPORT within ODS EXCEL, and my TITLE rows on the Excel file are coming out with a very large row height. The following code produces Row 1 with a height of 101, Row 2 with a height of 84, and Row 3 with a height of 67. Any suggestions as to how to keep the title rows a reasonable height?

ODS EXCEL FILE = "&Out_Path/MAQ_&EXCEL_FILE_NAME..xlsx"

OPTIONS(SHEET_NAME = "Members"

EMBEDDED_TITLES='YES'

GRIDLINES='ON'

FROZEN_HEADERS='YES'

ABSOLUTE_COLUMN_WIDTH = '12, 50, 12, 12, 12'

);

TITLE J=L 'CUSTOMER: ' &POLICY_REPORT_TITLE. ' - ' &CUST_NAME.;

TITLE2 J=L 'COVERAGE DATES: ' &FIRSTSERV. ' - ' &LASTSERV.;

TITLE3 J=L 'REPORT RUN DATE: ' &TODAY_DT.;

PROC REPORT DATA=DEVDATA.MBRSUM MISSING NOWD SPLIT='*';

COLUMN PVRC GROUP MONTH EMPMONTHS MBRMONTHS;

DEFINE PVRC / DISPLAY 'PVRC' STYLE(COLUMN)={JUST=L};

DEFINE GROUP / DISPLAY 'PLAN' STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};

DEFINE MONTH / DISPLAY 'MONTH' F=$6. STYLE(COLUMN)={JUST=L};

DEFINE EMPMONTHS / DISPLAY 'EMPLOYEES' STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0_)'};

DEFINE MBRMONTHS / DISPLAY 'MEMBERS' STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0_)'} ;

RUN;

Re: ODS Excel PROC REPORT TITLE Formatting

ballardw — Tue, 15 May 2018 19:46:06 GMT

Have you tried specifying height?

such as

TITLE J=L height=12pt 'CUSTOMER: ' &POLICY_REPORT_TITLE. ' - ' &CUST_NAME.;

Your ODS style may be setting defaults for title size that are getting interpreted oddly by Excel. Another bit might be to try setting a style such as Meadow.

ODS EXCEL FILE = "&Out_Path/MAQ_&EXCEL_FILE_NAME..xlsx"

style=styles.meadow

OPTIONS(SHEET_NAME = "Members"

EMBEDDED_TITLES='YES'

GRIDLINES='ON'

FROZEN_HEADERS='YES'

ABSOLUTE_COLUMN_WIDTH = '12, 50, 12, 12, 12'

);

Re: ODS Excel PROC REPORT TITLE Formatting

Tina1202 — Wed, 16 May 2018 15:54:47 GMT

I tried the Height option. It did change the font size from 11 to 12, but didn't change the row height problem. What is "Meadow"?

I think I've solved my issue by using the ODS EXCEL option

TITLE_FOOTNOTE_NOBREAK='YES'.

The titles are coming out on one row without the row height being really big.