topic Re: Problem when opening RTF ODS tables in MS Word in ODS and Base Reporting

Problem when opening RTF ODS tables in MS Word

JustvanEs — Fri, 13 Dec 2013 10:36:23 GMT

Dear all.

I have a problem with the conversion of SAS RTF output to Word. I'm using SAS 9.3 with an RTF ODS output (via styles and proc template) to create statistical summary tables. Many times when I open the resulting RTFs in Word I find that Word seems to think that it can only fit a limited amount of rows on one page (leaving a lot of whitespace at the bottom of each page). It can be especially large when I use a footnote. It looks horrible and increases the size of the documents considerably. I would like to produce more optimal page layout with the number of rows per page closer to what can be fit. Does anybody know why this happens and whether you can force Word to display a larger number of rows on a page. For columns you can simply use the CELLWIDTH option, but I haven't found anything that controls the rows. I run my reports per page (BY pagord). Below is an example of my proc template and structure of the proc report. In the styles we only specify things like margins, font sizes, etc.

Many thanks in advance!

Kind regards,

Just

PROC TEMPLATE;

DEFINE STYLE styles.xxx;

PARENT=styles.printer;

STYLE Fonts from Fonts /

'TitleFont' = ("Times New Roman",8pt,Bold Italic)

'headingFont' = ("Times New Roman",8pt,Bold)

'footFont' = ("Times New Roman",8pt,Bold Italic)

'docFont' = ("Times New Roman",8pt)

'FixedHeadingFont' = ("Arial",9pt,Bold);

STYLE Table from Output /

frame = hsides

cellpadding = 0.021in

cellspacing = 0in

borderwidth = 0.01in;

STYLE Body from Document /

topmargin = 1.25in

bottommargin = 1.00in

rightmargin = 0.5in

leftmargin = 0.5in;

STYLE Header from Header /

frame = hsides

backgroundcolor = transparent

borderwidth = 0.01in;

END;

PROC REPORT NOWD DATA=xxx MISSING SPLIT='~';

BY pagord.;

COLUMN pagord xxx yyy zzz ;

DEFINE pagord / ORDER NOPRINT ORDER=INTERNAL;

DEFINE xxx / ORDER STYLE=[JUST=C WIDTH=1.1cm] "Xxxx";

DEFINE yyy / ORDER STYLE=[JUST=C CELLWIDTH=1.1cm] 'Yyyy';

DEFINE zzz / DISPLAY STYLE=[JUST=C CELLWIDTH=1.3cm] 'Zzzz';

RUN;

Re: Problem when opening RTF ODS tables in MS Word

ballardw — Fri, 13 Dec 2013 16:50:49 GMT

When dealing with ODS output it is a good idea to post the ods options as well as an example of your output. KEEPN and STARTPAGE=NO might be aplicable.

All of my solutions for tables and ODS RTF or Tagsets.RTF are pretty adhoc so I'm not sure which might help.

The footnote issue might be solveable by using either the POSTTEXT style element with a table or ODS TEXT as the SAS behavior for footnotes is bottom of page resulting in the excessive white space. The downside of POSTTEXT or ODS TEXT is they only appear once after a table if it splits across pages.

Re: Problem when opening RTF ODS tables in MS Word

Cynthia_sas — Fri, 13 Dec 2013 17:57:37 GMT

Hi,

Consider the following code and compare the 3 outputs. You can change the margins, you can change the fontsize, you can change the cellpadding and impact the number of rows that fit on a page.

Cynthia

options topmargin=3in bottommargin=3in;

ods rtf file='c:\temp\margin_examp1.rtf';

proc report data=sashelp.shoes(obs=100);

title '1) 3in margins using all defaults';

run;

ods rtf close;

options topmargin=3in bottommargin=3in;

ods rtf file='c:\temp\margin_examp2.rtf';

proc report data=sashelp.shoes(obs=100)

style(report)={cellpadding=1px fontsize=8pt};

title '2) 3in margins, but changing defaults get more rows';

run;

ods rtf close;

options topmargin=.5in bottommargin=.5in;

ods rtf file='c:\temp\margin_examp3.rtf';

proc report data=sashelp.shoes(obs=100)

style(report)={cellpadding=1px fontsize=8pt};

title '3) .5in margins, but changing defaults get more rows';

run;

ods rtf close;

Re: Problem when opening RTF ODS tables in MS Word

JustvanEs — Mon, 16 Dec 2013 08:56:24 GMT

Many thanks for all your help sofar! Maybe I should have been more specific in the details of my output. I've further tested and I think the main issue is the following:

Background:The tables we create usually span across multiple pages, but we build our proc report per page (BY pagord in my example). Mainly just to have the control to change the footnotes across pages of the same table. With tables, we always want to keep table layout as consistent as possible across pages of the same table. Column X should have the same width across all pages of the table.

If we don't restrict (set any widths for columns, etc.) this will result in Word determining for each page of the table the width of columns by itself. Columns will then (usually) have varying width across pages, depending on the length of the actual content for that page. So we started setting the width of each column to the optimal width by hand (via the Style option in the define statement). We then have nice consistency in column widths across pages. However, when we do this, we suddenly get a dramatic decrease in the number of rows we can fit on a page (according to Word). Based on visual inspection I would say that the cellwidths we use are large enough to fit the content of the page. I see no wrapping of text or any other strange things, but Word seems to somehow think that it can only fit a smaller number of rows on a page with these settings. Footnotes seem to take a bit more space than strictly needed, but this is not a major issue.

So, I've tested 2 options:

a) set the width of each cell by hand (via STYLE(column) in the define statements

b) setting a limit on the total proc report width (via STYLE(report) in the proc report statement)

The header is the same in both cases as are all other details of the reports, but with option b) I can fit 29 rows on 1 page, while I am lucky to get 19 rows on a page with option a).

Can anybody explain why I get this difference? Many thanks for all your help!

Kind regards,

Just

Re: Problem when opening RTF ODS tables in MS Word

Cynthia_sas — Mon, 16 Dec 2013 14:35:43 GMT

Hi, As I indicated in my post, many different factors can impact the output you create. I find it useful to either set the width for the entire report OR set the individual column widths, but not to do both. I am not sure why you are seeing such a dramatic difference in the number of rows per page. I would have expected a few rows difference, but not that many. Have you looked at TAGSETS.RTF??? Generally vertical measurement issues are not as troublesome with this destination, because with "regular" RTF, Microsoft Word controls page breaking, which is why you have to resort to "fake" page break variables, but with TAGSETS.RTF, the vertical measurement is controlled by SAS. So this might be a useful destination to try.

However, I really think that you need to work with Tech Support on this issue. Because the difference in the number of rows is so dramatic, I would say that someone needs to look at ALL of your data and ALL of your code, including your ODS statements and margin options. You only showed the STYLE template and PROC REPORT, but didn't show your ODS statements or indicate your margin settings -- as you can see in my examples, the margins will make a difference in the number of rows that fit on the page, so will CELLPADDING, so there are other factors that just CELLWIDTH to take into account and Tech Support is best equipped to help you.

cynthia