ODS RTF spanrows does not repeat value after page break

[GGO]

This simple code confirms that the ODS RTF destination does not support the very useful SPANROWS option.

The TAGSETS.RTF destination has other issues the prevent use.

I haven't found a suitable solution online (communities.sas, stackoverflow, …).

Does anyone have a straightforward approach to achieving SPANROWS for ODS RTF destination, without over-complicating page break logic?

Any intention from SAS to update ODS RTF?

options ls=100 ps=25 orientation=landscape papersize=letter;

ods _all_ close;
ods rtf style=styles.statistical
    image_dpi=192
    startpage=never
    file='.\test_proc_report_spanrows.rtf' ;

data zipcode;
  set sashelp.zipcode (where=(statename='California'));
  if ranuni(16359) < 0.03;
run;

proc report data = zipcode missing noWindows split = '|' 
     spanrows
     style(report) = [width=8in];
  cols (StateName (State CountyNm Timezone))
       City Zip;

  define statename / order ;
  define state     / order;
  define countynm  / order;
  define timezone  / order;
  define city      / display;
  define zip       / display;
run;

ods _all_ close;
ods listing;

Just to confirm, no repeat of spanned values after a page break:

• 

[Cynthia_sas · Accepted solution]

Hi:

  This Tech Support note http://support.sas.com/techsup/notes/v8/7/887.html indicates that "In the Listing destination, when a variable defined as GROUP or ORDER breaks across a page, its value is repeated at the top of the subsequent page. This does not occur in the ODS PRINTER (PCL, PDF, PS) or the ODS RTF destinations. Due to vertical measurement issues, this feature will not be added to the ODS RTF destination." There is a workaound on the full code tab that is suggested.

  I do not know whether the newest ODS WORD destination in 9.4M6 will have this same issue or not. That would be a question for Tech Support.

Cynthia

[GGO]

Thanks for this reference, Cynthia.

The work-around works beautifully!

[Jianmin]

Hi Cynthia and GGO, 

The SAS Tech Support work-around only works for a nicer data.   This is called pagination problem of ODS RTF with Proc Report.  The SAS ODS RTF tries its best to do some calculation based on the page information in the input data, but it will never do the calculation right and it's time for a change in ODS RTF with Proc Report.   The solution is right there at the inception of ODS RTF: do not ask for page information, let Microsoft Word do the job.  

Attached SAS code shows the Tech Note does not work for a general data. 

Jianmin 

/* Create a dummy data set. */
data new;
   do i=1 to 100;
      if i < 72 then type='first';
      else type='last';
      newval='newval'||left(i);
      output;
   end;
run;

data new; set new; 
length newvals $200; 
retain newvals; 
x=floor(uniform(0)*10)+1; 
newvals=""; 
do i=1 to x; newvals=compress(newvals||newval); end; 
output; 
run; 

/* Sort the data set BY the GROUP/ORDER variable(s)
   in the PROC REPORT step.  */
proc sort data=new; 
   by type;
run;

/* PAGEIT increments once based on every 25 observations in FLAG
   grouping. FLAG is not needed in PROC REPORT, but is necessary
   in resetting PAGEIT. FLAG value of 25 is picked in this instance
   in order to paginate after every 25 detail rows - this number
   can vary based on font size, margin settings, orientation, etc. */

data reset;
   set new;
   by type;
   if _n_ eq 1 then flag=0;   
   else flag+1;
   if flag >=5 then do;
      pageit+1; 
      flag=0;
   end;
run;

ods pdf file='~/sasout/mypdf.pdf';
ods rtf file='~/sasout/myrtf.rtf';

proc report data=new nowd;
   title 'No repetition of TYPE when VALUE breaks across pages';
   column type i newvals;
   define type / order order=internal;
   define i    / display;
run;
/* PAGEIT is set to NOPRINT as it will only be used for pagination. */
proc report data=reset nowd ;
   title 'Workaround using dummy var - PAGEIT to force pagebreaks and
      repetition of TYPE values';
   column pageit type i newvals;
   define pageit / order order=internal noprint;
   define type   / order order=internal style(column)=[cellwidth=50%];
   define i      / display style(column)=[cellwidth=15%]; 
   define newvals / style(column)=[cellwidth=5%];
   break after pageit / page;
run;
ods _all_ close;

[GGO]

Hi Jianmin - I don't think your code works as expected. At least not for me. Or I don't understand the point you are trying to make.

I don't think either of these PROC REPORTS works as intended for RTF. Fewer rows fit on the RTF page for long NEWVALS strings.

Could you point out the solution that you've found "right there at the inception of ODS RTF"? I'm missing it.

[Jianmin]

Hi GGO, 

My code is a counter example to show the work-around is not working in general case.  In that code, no matter how you change the page information, unless you set one row per page, ODS RTF with Proc Report will never get it right for this randomly generated data.  If you Google "pagination ODS RTF Proc Report", you will see a few that mentioned how bad is the problem and proposed solutions.  In the end, they still rely on PROC REPORT page break to work out the magic, like the SAS Tech Note does.   The way ODS RTF works out this page breaking is that: the user provides information about paging, for example, for a page with 25 rows of a table, ODS will glue next 25 rows together, by using RTF key word \keepn 24 times, no matter what's in those 25 rows.  Or for that matter, ODS does not consider what's in a particular cell.  You can have a whole paragraph in one cell, that cell will wrap the paragraph in many lines.   A few other proposed solutions try to count how many lines that paragraph takes, and adjust the page information accordingly.  But it will never work out, if you leave it to ODS to do the rest, based on the information a user provided.  They only solved the problem for their data, at best. 

The solution for this problem is to ask ODS RTF to change the way it does with table rows.  In MS Word, a row, or a cell for that matter, is treated as a paragraph.  A paragraph can break in two pages, if it's too long.   Or stay on one page, if the user requests and it's possible to do so.  See the attached file: there are two rows in that table.  I checked a box in the first row table property as "Repeat as header row at the top of each page", and the other box unchecked (it doesn't matter).   You can take a look at the second row property (I didn't change anything there).  So, if I copy this email and past in a cell of the second row, you will see how MS Word works out. 

I hope this answers your question, and I hope we can give SAS ODS team some hint to solve the problem once for all. 

Many Thanks for your original question and interest to find out a solution. 

Best Regards, 

Jianmin 

[GGO]

Hi Jianmin - I think you're still missing the point of the SAS Tech note - the part that Cynthia bolded: "Due to vertical measurement issues, this feature will not be added to the ODS RTF destination." SAS is not able to add the spanrows functionality to the ODS RTF destination.

The work-around is that: just a work-around, not an ideal solution.

You have two choices:

1. SAS basic work-around works fine for data that does not have highly variable lengths - every obs needs the same number of lines on a page. If you can rely on every obs taking the same space on the page, then you can have uniform pages/page breaks (SAS basic work-around).
2. DATA-LENGTH-WEIGHTED paging. If you have some really long data, which require more lines on a page, give them a weight greater than 1 in the basic work-around. Depending on length of data, these obs require 2 or 3 or more lines, not just uniformly one (as in the basic work-around). Tweak the basic work-around with this data-length-weighting logic, and you can mimic SPANROWs just fine in ODF RTF destination. "Mimic", of course - it's a work-around, not a solution.

[Jianmin]

Hi GGO, 

Your points are well taken.  Let me see what's really missing here:  Spanrows is a SAS half-baked invention, it works really well in many cases.  But it really should be called SpanHeaderRow.  SAS reinvented a squared wheel and plus a few workarounds.  A traditional table starts with a header row and follows with a few rows.  Microsoft Word already worked out this type of tables.  Business people traded a traditional header row with a few columns when they see fit.  Like Subject #, Treatment, Age and so on.  When it comes to make a table, they wanted to skip some repeated rows and that's how Spanrows comes.  If SAS would go back to Microsoft Word's original idea, or add it as an option to users, PROC REPORT would not need a page break forever.  When you type in Word, you never thought about where to break a page, right?  Try the table in the previous reply, you can keep typing, coping and pasting, to see how a table grows and Microsoft Word works. 

Still, many thanks for your curiosity. 

Best Regards

Jianmin