How to split a PROC REPORT table into 2+ ODS RTF pages?

[BigPete]

Input:

Student Quiz Grade
Joe 1 C
Joe 2 A
Joe 3 A
Sue 1 A
Sue 2 B
Sue 3 B
Bob 1 C
Bob 2 B
Bob 3 C
Deb 1 B
Deb 2 B
Deb 3 A

Code:

%let dir=C:\JohnDoe;

proc import datafile="&dir\grades.xlsx"
			out=grades
			replace;
run;

ODS LISTING CLOSE;

OPTIONS NODATE
		NONUMBER
		TOPMARGIN	=0.5IN 
		LEFTMARGIN	=0.5IN
		RIGHTMARGIN =0.5IN
		BOTTOMMARGIN=0.5IN;

/*%let ftype=PDF;*/
%let ftype=RTF;
/*%let fstyle=&ftype;*/
%let fstyle=tagsets.&ftype;

ods noptitle;
ods &fstyle FILE = "&dir\test.&ftype" STYLE=BARRETTSBLUE options(doc='Help' tables_off='USERTEXT');

ods proclabel='Frequencies';

ODS ESCAPECHAR = '^';

ODS &fstyle TEXT = "^{sectd}^{STYLE[FONTSIZE=10.5pt JUST=L fontweight=bold]QUIZ}";
ODS &fstyle TEXT = " ";

PROC REPORT DATA = grades MISSING STYLE(REPORT)=[width=100% BACKGROUND=WHITE BORDERCOLOR=WHITE BORDERWIDTH=0 ASIS=OFF FONTSIZE=2] NOWD contents="quiz: &vlabel";

COLUMN quiz quiz=vallabel DUMROW N PCTN;

COMPUTE quiz;
	COUNT+1;
/*	IF (MOD(COUNT,2)) THEN DO;*/
/*	CALL DEFINE (_ROW_, 'STYLE', 'STYLE=[BACKGROUND=WHITE]');*/
/*	END;*/
  if (mod(count,2))=0 then call define(_row_, 'STYLE', 'STYLE=[BACKGROUND=WHITE]');
ENDCOMP;

DEFINE quiz	/"Value" GROUP ORDER=INTERNAL	STYLE(COLUMN)=[WIDTH=10% JUST=C FONTSIZE=2]	STYLE(HEADER)=[JUST=C FONTSIZE=2]	;
DEFINE vallabel	/"Label" GROUP ID				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2] format=1.	NOPRINT	;
DEFINE DUMROW	/COMPUTED "Label"				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2]	width=65;
DEFINE N 		/"Frequency"			STYLE(COLUMN)=[WIDTH=15% JUST=R FONTSIZE=2]	STYLE(HEADER)=[JUST=R FONTSIZE=2]	FORMAT=COMMA12.			;
DEFINE PCTN		/"%"							STYLE(COLUMN)=[WIDTH=10% JUST=R FONTSIZE=2]	STYLE(HEADER)=[JUST=R FONTSIZE=2]	FORMAT=PERCENT7.1		;

COMPUTE DUMROW /CHAR LENGTH = 65;
	IF _BREAK_="_RBREAK_" THEN DO DUMROW = 'Total';
	END;
	ELSE DUMROW=PUT(vallabel, 1.);
	IF DUMROW = 'Total' THEN CALL DEFINE (_ROW_, 'STYLE', "STYLE=[BACKGROUND=LIGHT GRAY FONTSTYLE=ITALIC FONTSIZE=2]");
ENDCOMP;

RBREAK AFTER / SUMMARIZE;

RUN;

ODS &fstyle TEXT = " ";
ODS &fstyle TEXT = "AND THAT'S A WRAP FOR QUIZZES";

quit;
title;
ODS _all_ CLOSE;

What would I need to tweak in my code to make a PROC REPORT table split from this

into multiple pages like these?

[ballardw]

See if this gets close. Note that the data set "example" did not have variables N or PCTN so i dropped them from the code.

data have;
   input Student $ Quiz Grade $;
datalines;
Joe 1 C
Joe 2 A
Joe 3 A
Sue 1 A
Sue 2 B
Sue 3 B
Bob 1 C
Bob 2 B
Bob 3 C
Deb 1 B
Deb 2 B
Deb 3 A
;

ods rtf file="D:\Users\Owner\Documents\example.rtf";
PROC REPORT DATA = have  MISSING STYLE(REPORT)=[width=100% BACKGROUND=WHITE BORDERCOLOR=WHITE BORDERWIDTH=0 ASIS=OFF FONTSIZE=2] NOWD contents="quiz: &vlabel";

COLUMN quiz quiz=vallabel DUMROW ;

COMPUTE quiz;
	COUNT+1;
/*	IF (MOD(COUNT,2)) THEN DO;*/
/*	CALL DEFINE (_ROW_, 'STYLE', 'STYLE=[BACKGROUND=WHITE]');*/
/*	END;*/
  if (mod(count,2))=0 then call define(_row_, 'STYLE', 'STYLE=[BACKGROUND=WHITE]');
ENDCOMP;

DEFINE quiz	/"Value" GROUP  ORDER=INTERNAL	STYLE(COLUMN)=[WIDTH=10% JUST=C FONTSIZE=2]	STYLE(HEADER)=[JUST=C FONTSIZE=2]	;
DEFINE vallabel	/"Label" GROUP ID				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2] format=1.	NOPRINT	;
DEFINE DUMROW	/COMPUTED "Label"				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2]	width=65;

COMPUTE DUMROW /CHAR LENGTH = 65;
	IF _BREAK_="_RBREAK_" THEN DO DUMROW = 'Total';
	END;
	ELSE DUMROW=PUT(vallabel, 1.);
	IF DUMROW = 'Total' THEN CALL DEFINE (_ROW_, 'STYLE', "STYLE=[BACKGROUND=LIGHT GRAY FONTSTYLE=ITALIC FONTSIZE=2]");
ENDCOMP;

RBREAK AFTER / SUMMARIZE;
break after quiz/page;

RUN;

ods rtf close;

[BigPete]

I think I found some clues from this posting: https://communities.sas.com/t5/ODS-and-Base-Reporting/proc-report-page-break/td-p/79140

%let dir=C:\Johnny;

proc import datafile="&dir\grades.xlsx"
			out=grades
			replace;
run;

data grades;
  set grades;
  pgbrk=ceil(divide(quiz,2));
run;

ODS LISTING CLOSE;

OPTIONS NODATE
		NONUMBER
		TOPMARGIN	=0.5IN 
		LEFTMARGIN	=0.5IN
		RIGHTMARGIN =0.5IN
		BOTTOMMARGIN=0.5IN;

/*%let ftype=PDF;*/
%let ftype=RTF;
/*%let fstyle=&ftype;*/
%let fstyle=tagsets.&ftype;

ods noptitle;
ods &fstyle FILE = "&dir\test.&ftype" STYLE=BARRETTSBLUE options(doc='Help' tables_off='USERTEXT');

ods proclabel='Frequencies';

ODS ESCAPECHAR = '^';

ODS &fstyle TEXT = "^{sectd}^{STYLE[FONTSIZE=10.5pt JUST=L fontweight=bold]QUIZ}";
ODS &fstyle TEXT = " ";

PROC REPORT DATA = grades MISSING STYLE(REPORT)=[width=100% BACKGROUND=WHITE BORDERCOLOR=WHITE BORDERWIDTH=0 ASIS=OFF FONTSIZE=2] NOWD contents="quiz";

COLUMN pgbrk quiz quiz=vallabel DUMROW N PCTN;

COMPUTE quiz;
  COUNT+1;
  if (mod(count,2))=0 then call define(_row_, 'STYLE', 'STYLE=[BACKGROUND=WHITE]');
ENDCOMP;

define pgbrk / group noprint;
DEFINE quiz	/"Value" GROUP ORDER=INTERNAL	STYLE(COLUMN)=[WIDTH=10% JUST=C FONTSIZE=2]	STYLE(HEADER)=[JUST=C FONTSIZE=2]	;
DEFINE vallabel	/"Label" GROUP ID				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2] format=1.	NOPRINT	;
DEFINE DUMROW	/COMPUTED "Label"				STYLE(COLUMN)=[WIDTH=60% JUST=L FONTSIZE=2]	STYLE(HEADER)=[JUST=L FONTSIZE=2]	width=65;
DEFINE N 		/"Frequency"			STYLE(COLUMN)=[WIDTH=15% JUST=R FONTSIZE=2]	STYLE(HEADER)=[JUST=R FONTSIZE=2]	FORMAT=COMMA12.			;
DEFINE PCTN		/"%"							STYLE(COLUMN)=[WIDTH=10% JUST=R FONTSIZE=2]	STYLE(HEADER)=[JUST=R FONTSIZE=2]	FORMAT=PERCENT7.1		;

COMPUTE DUMROW /CHAR LENGTH = 65;
	IF _BREAK_="_RBREAK_" THEN DO DUMROW = 'Total';
	END;
	ELSE DUMROW=PUT(vallabel, 1.);
	IF DUMROW = 'Total' THEN CALL DEFINE (_ROW_, 'STYLE', "STYLE=[BACKGROUND=LIGHT GRAY FONTSTYLE=ITALIC FONTSIZE=2]");
ENDCOMP;

RBREAK AFTER / SUMMARIZE;
break after pgbrk / page;

RUN;

ODS &fstyle TEXT = " ";
ODS &fstyle TEXT = "AND THAT'S A WRAP FOR QUIZZES";

quit;
title;
ODS _all_ CLOSE;

The idea of creating a "page break" variable was a major clue. However it spat out the 'total' row and concluding texts into a separate page when I need to include them together with last page of summary contents. Any idea?

@Cynthia_sas?

[Cynthia_sas · Accepted solution]

Hi:

  With a break after, you will almost always get the last RBREAK on a separate page because the PGBRK variable is not relevant to a break line that PROC REPORT is inserting based on the presence of an RBREAK statement. The BREAK AFTER PGBRK forced the break after the last value of PGBRK on the report and then there was no other place for the RBREAK to go.

  So I made a fake RBRK variable as well as a PGBRK variable. I don't open Excel files, so I used the DATA step that was so helpfully posted for testing:

  Then I did some cosmetic tweaks to the code. First, I got rid of all the distractors -- the macro variables were just distracting to me, so I hardcoded everything to make it easier to read. Also, the WIDTH in percents just didn't make sense to me because some of the widths were on NOPRINT items. So I took those off. The FONTSIZE as a relative number of 2 was just forcing ODS to convert the relative number to a PT size (for RTF or PDF), so I just specified a PT size and moved the FONTSIZE override into the PROC REPORT statement so it wouldn't have to be on every DEFINE statement. The benefit of this was simplifying the DEFINE statements. Not sure why you need VALLABEL as an alias for QUIZ since all you do is use it to calculate a value for DUMROW, which you could just as well do with QUIZ.

  When I do PROC REPORT code, I like to have all my DEFINE statements first, followed by all the COMPUTE statements. The order of the statements doesn't matter, but behind the scenes, PROC REPORT collects all the COMPUTE blocks so it can executes them at the proper time based on the COLUMN statement. Since I know that PROC REPORT does this pre-processing, I like to have all my COMPUTE statements organized after all the DEFINES. It didn't make any difference but it's my preference, so that's what I did.

  Changing the PGBRK to a BREAK BEFORE PGBRK allowed your TOTAL row to be at the bottom without the issue you were having before.

  I think changing the rows based on the calculate COUNT is a bit fiddly. Using BREAK BEFORE changed the value of COUNT, so that the first data row was 2. I fiddled with the logic, but honestly, you've only got 2 rows per page, which seems like it causes a wasted huge white space on such large pages in RTF ( .5 in at top and bottom makes for a 10" page area on a portrait page) and I'm not sure that fiddling with the background colors is worth it.

Cynthia

Here's my final attempt -- I think I manged to highlight most of the major things I changed.

[BigPete]

Thanks Cynthia