Controlling page breaks when using proc report

[_Manhattan]

Hello,

I am struggling with controlling page breaks in proc report/ods PDF. I already did a post a few months ago (https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-suppress-table-splitting-in-ODS-PDF/m-p...), but I have not found a solution yet. First, I thought I will just use ods rtf with the keepn option, but that brings more problems than it solves. Now, I have just found a paper that kind of describes the problem from Stetz et al. "Controlling Page Breaks when using Proc Report" (https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://support.sas.com/resources/...). Unfortunately, the paper is quite short and I do not understand how the data in the example code is structured. My question is: Does anyone have further knowledge on using "line counting" to control page breaks with proc report? Or does anyone know other papers that talk about this issue?

@Cynthia_sas did you write the paper? And if so, have you got any tips for the page break control?

If anyone needs code examples I can provide code and data, but for now I thought it would be more useful if I try different approaches before just posting my code where I did not achieve much yet. I am grateful for any help.

Kind regards

[Cynthia_sas]

Hi:
Sorry, this is an older paper from 1995 I believe and the author is listed as Cynthia Stetz from Merrill Lynch. I think this is only partly relevant to you because in 1995, there was not any ODS RTF or even ODS at all and she was writing to the SAS Listing window, which does not apply in your case. At any rate, I think there are other examples of creating your own page break variable for ODS RTF posted here in Communities. But I do have an example using 100 rows from SASHELP.SHOES. I will post that example.
Cynthia

  You should be able to see the structure of SASHELP.SHOES before the program runs and then look at the FAKE_SHOES data to see the value of the PGBRK variable. In this example, I arbitrarily wanted to show setting the PGBRK variable to only put 20 rows per page and summarize at the end of every 20 rows:

data fake_shoes;
set sashelp.shoes(obs=100); 
** want to put only 20 rows per page;
if _n_ le 20 then pgbrk=1; 
else if 21 le _n_ le 40 then pgbrk=2;  
else if 41 le _n_ le 60 then pgbrk=3;  
else if 61 le _n_ le 80 then pgbrk=4;  
else if 81 le _n_ le 100 then pgbrk=5;  

if pgbrk le 5 then output;
run;

options orientation=portrait ;
  
ods rtf file='c:\temp\use_break.rtf';
  proc report data=fake_shoes;
    title 'Show PGBRK variable';
	title2 'Can hide variable using NOPRINT option';
    column pgbrk region subsidiary sales inventory returns;
	define pgbrk / group;
	define region / order;
	define subsidiary / order;
	define sales/sum;
	define inventory /sum;
	define returns/sum;
    break after pgbrk / page summarize;
  run;
ods rtf close;

[_Manhattan]

Thank you @Cynthia_sas and thank you @Ksharp for your replies which are helpful as always!

I think the proposed solutions work fine if you have the same pattern of tables repeating over and over again (e. g. you always need 20 lines per table) or if you have one big table which you want to split at a given point like described in the Shan (2012) paper. Unfortunately, I am having trouble with implementing this logic into my macro/do loop.

I think my problem is, that I would need to calculate the overall lines available per page (sth. around 45) and then tell proc report to write the upcoming dataset on a new page if the available space is exceeded by this dataset. For that, I would need to automatically calculate the lines per dataset and tell SAS to check if the lines per dataset still fit as a whole on the page (and if not skip to the next page). Or at least I think that could be a solution. I have tried to implement your solutions in my code, but I did not even manage to generate a simple page break with the pgbrk variable method from Cynthia. Furthermore, I would need to insert the page breaks not just for the "VariableInfo" data, but also for the freq and mean datasets.  My Code looks like this (I know its rubbish data, but it does the job to show my problem). I have added some comments as well:

proc datasets lib=work nolist; delete sample_data input_data mean: VariableInfo freq: ;run ;quit;

%let yourpath=c:\;

ods listing;
/*** Example Data ***/
data work.sample_data;
  input EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;
  datalines;
  10 20 30 40 50  1 2 3 2 3
  15 25 35 45 55  1 3 4 3 2
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  ;
run;

data work.input_data;
   length Info1 $1000 Info2 $1000;
   input Info1 $ Info2 $ Sort;
   infile datalines delimiter=",";
   datalines;
   Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test,1
   AGHK50A,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test,1
   AGHK50B,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,1
   AGHK50C,Item C Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test SpaceTest,1
   Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   BGKO28A,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   BGKO28B,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   EZGH25A,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   EZGH25B,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   EZGH25C,Item C Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   EZGH25D,Item D Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   EZGH25E,Item E Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
;

/* Using proc sql for a Sorting Variable which is used to iterate trough the do-loop */
proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

%let varlistE = EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E;
%let varlistA = AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;

/* Mean Calculation */
proc means data=work.sample_data noprint;
  var &varlistE;
  output out=work.mean_ezgh;
run;

%macro meandata;
   %do i = 1 %to %sysfunc(countw(&varlistE));
   %let vmean = %scan(&varlistE, &i);
   
   data work.mean&vmean;
      set work.mean_ezgh (keep=&vmean);
   run;
   
   %end;
%mend meandata;
%meandata;

/* Freq calculation */
%macro freqdata;
   %do i = 1 %to %sysfunc(countw(&varlistA));
   %let vfreq = %scan(&varlista, &i);
   
   proc freq data=work.sample_data;
      tables &vfreq/ out=work.freq&vfreq;
   run;
   
   %end;
%mend freqdata;

%freqdata;


/*** Macro Loop ***/

%macro Testloop;
  %do i = 1 %to %sysfunc(countw(&Sort.));
    %let SortID = %scan(&sort, &i);
    %let VariableA = %scan(&varlistA, &i);
    %let VariableE = %scan(&varlistE, &i);
    

/* Compute Variable Information for each Variable */
    data VariableInfo;
      set work.input_data;
      if sort eq &i then pgbrk=&i;
      if pgbrk le 3 then output;
      where Sort = &SortID.;
      drop Sort;
    run;
    
   %local Info1Items;
   %let Info1Items=;
   PROC SQL noprint;
      SELECT distinct Info1 INTO :Info1Items SEPARATED BY " "
      FROM work.input_data
      WHERE Sort = &SortID.
      AND Info1 not in ('' 'Variable');
   QUIT;


   ods pdf startpage=no; /* Not sure if the startpage option is causing problems with the explicit page breaks - but I guess it should not */
   ods layout gridded columns=1; 
   ods region;
/* Report Variable Information for each Variable */
    proc report data=VariableInfo noheader;
      column pgbrk Info1 Info2;
      define pgbrk / group noprint;
      break after pgbrk / page; /* I do not unterstand why it does not insert a page break afterwards. Maybe because the pgbrk variable is the last and not the first variable in the dataset? */
    run;
    
ods layout end;
    
   %MACRO PrinItemStats(Item=,colPosition=);
   ods layout gridded columns=1;
      ods region;
      %if %sysfunc(exist(freq&Item.)) %then %do; 
         /* Print Frequency Table */
         proc report data=freq&Item.; /* How could I implement the page breaks when I add several proc report steps like here? */
            column &Item. COUNT PERCENT;
            define &Item / display "&Item.";
            define COUNT / display "Absolut";
            define PERCENT / display "Percent";
         run;
      %end;

      %if %sysfunc(exist(mean&Item.)) %then %do; 
         /* Print Mean Table */
         proc report data=mean&Item.;
            column &Item.; 
         run;
      %end;
      
   ods layout end;
   %MEND PrinItemStats;


   %local j currItem;
   %LET j=1;
   %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));


   %DO %WHILE(%LENGTH(&currItem.)>0);


      %PrinItemStats(Item=&currItem.);

      %LET j=%EVAL(&j.+1);
      %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));
   %END;


  %end;


%mend Testloop;

ods _all_ close;
options  papersize=a4 orientation=portrait leftmargin=1.5cm rightmargin=1.5cm topmargin=1.5cm bottommargin=1.5cm nodate nonumber;
ods pdf file="&yourpath.\Loop.pdf";
%Testloop;
ods pdf close;

[Cynthia_sas]

Hi: My recommendation is to get your basic page break logic working WITHOUT using any macro code to make sure that your page break logic is correct for at least one case before you go down the road of trying to "macro-ize" your code. Debugging a program that does not work when you have complicated it with a Macro program can be tough. Do you have a working example for just ONE scenario? It looks to me like you are only printing 1 page at a time in your loop, using &i for the pgbrk variable in the code you show. This worked better for me.

  I used your original WORK.INPUT_DATA file and then modified the code so there would be at least 2 pages on the report. I chose Portrait as the orientation just to show how the line wraps automatically and because it's easier to show the pages side-by-side that way:

Cynthia

[_Manhattan]

Edit: I have written a whole paragraph about how "startpage = no" is interfering with my page break logic, but now I've realized that I do not need startpage = no in my code. I will try some stuff out for now

[ballardw]

---

@_Manhattan wrote:

Thank you @Cynthia_sas and thank you @Ksharp for your replies which are helpful as always!

I think the proposed solutions work fine if you have the same pattern of tables repeating over and over again (e. g. you always need 20 lines per table) or if you have one big table which you want to split at a given point like described in the Shan (2012) paper. Unfortunately, I am having trouble with implementing this logic into my macro/do loop.

I think my problem is, that I would need to calculate the overall lines available per page (sth. around 45) and then tell proc report to write the upcoming dataset on a new page if the available space is exceeded by this dataset. For that, I would need to automatically calculate the lines per dataset and tell SAS to check if the lines per dataset still fit as a whole on the page (and if not skip to the next page). Or at least I think that could be a solution. I have tried to implement your solutions in my code, but I did not even manage to generate a simple page break with the pgbrk variable method from Cynthia. Furthermore, I would need to insert the page breaks not just for the "VariableInfo" data, but also for the freq and mean datasets.  My Code looks like this (I know its rubbish data, but it does the job to show my problem). I have added some comments as well:

Throwing ODS LAYOUT into PAGE definitions makes things much more complicated as to where a "page breaks". ODS Regions can by themselves span more than one page. So where any one region starts already has a problem with creating a define page break as you don't know where it starts with certainty, especially for the second and subsequent regions.

A bit complicated as your Testloop macro defines no parameters and assumes things exist that may not when the macro starts such as the macro variable Sort. This is dangerous as when you don't show where that variable is defined the results are a bit problematic as well.

[_Manhattan]

Hey @ballardw thank you for your reply! I feel like I do not have much knowledge about how ods region behaves. I have already thought about the possibility to determine exactly where and when to start a new page. But I guess for my case it is sufficient to know approximately the size of the page and to "tell" SAS to start a new page if it is noticing there is not enough space for the next proc report step. Even if the page break would not be perfect.

Regarding the Sort Variable there is actually this proc sql part inside the provided code:

proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

Although, I am not sure if that is what you mean or if I am overseeing other non-defined parameters?

[Cynthia_sas]

Hi: When you are talking about combining the results from 3 different procedures (REPORT, FREQ and MEANS) in one report, my mind goes to ODS DOCUMENT/PROC DOCUMENT. I don't think I would start with a Macro and ODS LAYOUT/ODS REGION until after I had tried using ODS DOCUMENT to store the original output objects and then replay them in the desired structure.
Cynthia

[_Manhattan]

Hey, actually I am just using proc report. The procedures I use before the macro already generate all tables (with my original data). I have tried to examplify the logic of my code with the sample_data and input_data as well as the frequency and mean calculations. But actually the proc means and proc freq steps are not important for my original data. In the end there are just 2 different proc report steps I have to handle for the page breaks. Or rather 3 in total, but in the macro loop there are always only 2 proc report steps that are applied, depending on whether it is a frequency or mean table that is reported.

[Ksharp]

I wrote a paper about such kind of topic a couple of years ago.

http://support.sas.com/resources/papers/proceedings12/389-2012.pdf