topic ods tagsets.excelxp title row height issue in ODS and Base Reporting

ods tagsets.excelxp title row height issue

eferencik — Fri, 20 Jul 2018 19:13:06 GMT

I am having issues with the report's title row heights not being consistent. Here is my ods statement.

ODS LISTING CLOSE;

ODS NORESULTS;

OPTIONS LeftMargin = .5in

RightMargin = .5in

TopMargin = .5in

BottomMargin = .5in

missing=' ';

ods tagsets.excelxp file="c:\CO Turnover Rate &title..xls"

style=minimal

OPTIONS (Orientation = 'landscape'

width_fudge='0.6'

absolute_COLUMN_WIDTH="12,10,10,10"

fittopage='yes'

Center_Horizontal='on'

EMBEDDED_TITLES='yes'

EMBEDDED_FOOTNOTES='yes'

autofit_height='on'

Pages_FitWidth='1'

Pages_FitHeight='100'

row_repeat='header');

ods tagsets.ExcelXP options(sheet_name="CO Turnover");

PROC REPORT DATA=FINAL NOWINDOWS MISSING

style(header)=[font_face=Times font_weight=bold background=ltgray]

style(column)=[font_face=Times];

COLUMNS DATE MON n EMPS TERMS PCT;

DEFINE DATE / ORDER=DATA NOPRINT;

DEFINE MON / DISPLAY 'Month';

DEFINE EMPS / ANALYSIS 'CO Count*' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"];

DEFINE TERMS / ANALYSIS 'Terminations' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"];

DEFINE PCT / ANALYSIS 'Turnover Rate' STYLE(COLUMN)=[TAGATTR="FORMAT:0.0#%"];

DEFINE N / NOPRINT;

COMPUTE AFTER;

DATE="&title";

EMPS.SUM=EMPS.SUM/N;

TERMS.SUM=TERMS.SUM;

PCT.SUM=TERMS.SUM/EMPS.SUM;

ENDCOMP;

rbreak after / dol skip summarize style(summary)=[background=ltgray font_weight=bold];

TITLE JUSTIFY=CENTER bold height=14pt font=times "Correctional Officer Turnover Rate";

title2 JUSTIFY=CENTER bold height=14pt font=times "&title";

title3 JUSTIFY=CENTER italic height=12pt font=times '(includes Job Class codes JD30, JD35, JD40 & JD50)';

RUN;

ODS TAGSETS.EXCELXP CLOSE;

ODS LISTING;

ODS RESULTS;

Here is the title and column header portion of my output. I don't understand why rows 1 and 3 are taller than row 2.

Re: ods tagsets.excelxp title row height issue

Reeza — Fri, 20 Jul 2018 19:32:54 GMT

I think its part of the different title issue. What happens if you use ODS TEXT = statements instead?

@eferencik wrote:

I am having issues with the report's title row heights not being consistent. Here is my ods statement.

ODS LISTING CLOSE;

ODS NORESULTS;

OPTIONS LeftMargin = .5in

RightMargin = .5in

TopMargin = .5in

BottomMargin = .5in

missing=' ';

ods tagsets.excelxp file="c:\CO Turnover Rate &title..xls"

style=minimal

OPTIONS (Orientation = 'landscape'

width_fudge='0.6'

absolute_COLUMN_WIDTH="12,10,10,10"

fittopage='yes'

Center_Horizontal='on'

EMBEDDED_TITLES='yes'

EMBEDDED_FOOTNOTES='yes'

autofit_height='on'

Pages_FitWidth='1'

Pages_FitHeight='100'

row_repeat='header');

ods tagsets.ExcelXP options(sheet_name="CO Turnover");

PROC REPORT DATA=FINAL NOWINDOWS MISSING

style(header)=[font_face=Times font_weight=bold background=ltgray]

style(column)=[font_face=Times];

COLUMNS DATE MON n EMPS TERMS PCT;

DEFINE DATE / ORDER=DATA NOPRINT;

DEFINE MON / DISPLAY 'Month';

DEFINE EMPS / ANALYSIS 'CO Count*' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"];

DEFINE TERMS / ANALYSIS 'Terminations' STYLE(COLUMN)=[TAGATTR="FORMAT:#,###"];

DEFINE PCT / ANALYSIS 'Turnover Rate' STYLE(COLUMN)=[TAGATTR="FORMAT:0.0#%"];

DEFINE N / NOPRINT;

COMPUTE AFTER;

DATE="&title";

EMPS.SUM=EMPS.SUM/N;

TERMS.SUM=TERMS.SUM;

PCT.SUM=TERMS.SUM/EMPS.SUM;

ENDCOMP;

rbreak after / dol skip summarize style(summary)=[background=ltgray font_weight=bold];

TITLE JUSTIFY=CENTER bold height=14pt font=times "Correctional Officer Turnover Rate";

title2 JUSTIFY=CENTER bold height=14pt font=times "&title";

title3 JUSTIFY=CENTER italic height=12pt font=times '(includes Job Class codes JD30, JD35, JD40 & JD50)';

RUN;

ODS TAGSETS.EXCELXP CLOSE;

ODS LISTING;

ODS RESULTS;

Here is the title and column header portion of my output. I don't understand why rows 1 and 3 are taller than row 2.

Re: ods tagsets.excelxp title row height issue

eferencik — Fri, 20 Jul 2018 19:50:25 GMT

How would I format that to have the formatting specified in my current title statements?

Re: ods tagsets.excelxp title row height issue

Reeza — Fri, 20 Jul 2018 20:20:40 GMT

It doesn't look like you can do inline styles, you have to do PROC TEMPLATE. PROC ODSTEXT has some options than an ODS TEXT statement will have, but a little more work as well.

http://documentation.sas.com/?docsetId=odsproc&docsetVersion=9.4&docsetTarget=p14gx25pepks6dn1q9m7vkq3gfoi.htm&locale=en#n0rx93bn3orev5n1ogar2yb813kr

@eferencik wrote:

How would I format that to have the formatting specified in my current title statements?