topic Re: ODS PDF listing: Breaking up a table values by group in SAS in SAS Procedures

ODS PDF listing: Breaking up a table values by group in SAS

1239 — Tue, 11 Oct 2011 12:57:10 GMT

Hi,

I have attached the sample report and sample report desired output pdf files. Actually I want the output as sample report desired.

The requirement is to break the manager details into next page. For example in the sample report Manager "Smith" row is appeared in the first page wherein it has to be moved into next page.

Whenever details are breaked partially into first and second page then then entire details to be printed into second page.

Please let me know how to achieve this.

ods pdf close;

ods listing;

ods pdf close;

ods listing close;

ods results off;

options papersize=A4 nodate;

options orientation=landscape leftmargin="0.2cm" rightmargin="0.2cm" topmargin="0.5cm" bottommargin="0.5cm";

goptions ftext = "helvetica/bold" noborder device=CGMOF97P NOGRAPHRC;

ods pdf style=statdoc file='sample report.pdf'

notoc style=statdoc;

title 'Sample Report';

proc report data=test nowd headline ;

define manager / order

order=formatted;

run;

title 'Sample Report';

run;

ods pdf close;

ods results on;

ods listing;

ODS PDF listing: Breaking up a table values by group in SAS

Peter_C — Tue, 11 Oct 2011 15:15:34 GMT

has REPORT the equivalent of PROC PRINT feature PAGEBY?

ODS PDF listing: Breaking up a table values by group in SAS

Tom — Tue, 11 Oct 2011 15:24:46 GMT

You can force a page break when value of manager changes, but I do not know of a way to "protect" a particular manager's rows from spanning two pages.

Re: ODS PDF listing: Breaking up a table values by group in SAS

1239 — Wed, 12 Oct 2011 06:37:50 GMT

Hi, Tom. Please let me know how force a page break when value of manager changes? Thanks

ODS PDF listing: Breaking up a table values by group in SAS

Ksharp — Wed, 12 Oct 2011 07:53:04 GMT

The following is the code I wrote before for this situation.

But you need to count the number of line in a page firstly. My code is 34.

And there is another workaround way that first keep it as rtf (ods rtf) then change it into PDF.

I am curious that Why SAS can not offer an option to do it.

data base;
 set sashelp.shoes;
 where ranuni(0) ge .5 ;
run;


ods listing close;
ods pdf file='c:\x.pdf'; * to test the page size of pdf;
proc print data=base;  *column obs is to see how many lines each page;
run;                    *34 is the number of lines in pdf;
ods pdf close;
ods listing;

proc means data=base nway;
 class region;
 output out=count n=count;
run;
data result;
 merge base count(keep=region count);
 by region;
run;
data result;
 set result;
 mod=mod(count,34);
run;
data result; * To decide Page break point;
 set result;
 by region;
 retain break 1;
 if last.region then sum_mod+mod;
 if count +sum_mod ge 34 and region ne lag(region) then do;
                                                                break+1;
                                                                sum_mod=0;
                                                                end;
run;
options nolabel;
ods listing close;
ods pdf file='c:\report.pdf';
proc report data=result nowd spanrows;
 column region product subsidiary stores sales inventory returns break;
  define break /group noprint;
  break after break /page;
run;
ods pdf close;
ods listing;

Ksharp

Re: ODS PDF listing: Breaking up a table values by group in SAS

1239 — Wed, 12 Oct 2011 10:29:07 GMT

Hi Ksharp,

Thanks for the code. The only change required is Region should be displayed only once (as per sample report desired output wherein only Manager displayed only once). I tried the below code but didn't work. Please help

options nolabel;

ods listing close;

ods pdf file='C:\Documents and Settings\q720439\report.pdf';

proc report data=result nowd spanrows;

column region product subsidiary stores sales inventory returns break;

define region / order

order=formatted;

define break /group noprint;

break after break /page;

run;

ods pdf close;

ods listing;

Re: ODS PDF listing: Breaking up a table values by group in SAS

data_null__ — Wed, 12 Oct 2011 12:26:15 GMT

This will only work for simple reports where the number of rows each observation produces can be predicted.

data test;
   input Manager $ Department :$16. Sales;
   cards;
Adams Canned      225
Adams Meat/Dairy  350
Adams Paper 40
Adams Produce     80
Alomar      Canned      420
Alomar      Meat/Dairy  190
Alomar      Paper 90
Alomar      Produce     86
Andrews     Canned      420
Andrews     Meat/Dairy  300
Andrews     Paper 200
Andrews     Produce     125
Brown Canned      230
Brown Meat/Dairy  250
Brown Paper 45
Brown Produce     73
Jones Canned      220
Jones Meat/Dairy  300
Jones Paper 40
Jones Produce     70
Pelfrey     Canned      420
Pelfrey     Meat/Dairy  205
Pelfrey     Paper 45
Pelfrey     Produce     76
Reveiz      Canned      420
Reveiz      Meat/Dairy  600
Reveiz      Paper 60
Reveiz      Produce     30
Smith Canned      120
Smith Meat/Dairy  100
Smith Paper 50
Smith Produce     80
Taylor      Canned      120
Taylor      Meat/Dairy  130
Taylor      Paper 53
Taylor      Produce     50
;;;;
   run;
proc summary data=test nway missing;
   class manager;
   output out=lpm(drop=_type);
data test2;*(drop=_:);
   merge test lpm;
   by manager;
   if first.manager then link ll;
   _ll + -1;
   retain _ll _ps 28;
   return;
 LL:
   if _ll lt _freq_ then do;
      page + 1;
      _ll = _ps;
      end;
   return;
proc print;
   run;
ods listing close;
ods results off;
options papersize=A4 nodate;
options orientation=landscape leftmargin="0.2cm" rightmargin="0.2cm" topmargin="0.5cm" bottommargin="0.5cm";
goptions ftext = "helvetica/bold" noborder device=CGMOF97P NOGRAPHRC;
ods pdf style=statdoc file='sample report.pdf' notoc style=statdoc;
title 'Sample Report';
proc report data=test2(drop=_:)  nowd headline list;
   column page manager department sales;
   define _all_ / display;
   define page / order noprint;
   define manager / order order=formatted;
   break before page / page;
   run;
ods pdf close;
ods results on;
ods listing;
title;

Re: ODS PDF listing: Breaking up a table values by group in SAS

Tom — Wed, 12 Oct 2011 12:34:43 GMT

See the post below from data_null_ for a more complete program.

The order of the variables in the COLUMN statement is important. BREAK should be the FIRST variable, not the last.

Not sure why you would use GROUP instead of ORDER for the BREAK variable. You are not requesting any grouped output.

proc report data=result nowd spanrows;

column break region product subsidiary stores sales inventory returns ;

define region / order order=formatted;

define break / order noprint;

break after break /page;

run;

You will also see in data_null_'s responce that he used

define _all_ / display;

This will prevent the PROC REPORT from treating the numeric variables as SUM variables.

Re: ODS PDF listing: Breaking up a table values by group in SAS

Ksharp — Thu, 13 Oct 2011 03:54:52 GMT

OK. I will do it for you.It is based on the dataset 'test' you offered which is under c:\

How about:

libname test v9 'c:\';



/*to test the page size of pdf*/
ods listing close;
          options papersize=A4 nodate;
          options orientation=landscape leftmargin="0.2cm" rightmargin="0.2cm" topmargin="0.5cm" bottommargin="0.5cm";
          goptions ftext = "helvetica/bold" noborder device=CGMOF97P NOGRAPHRC;
          ods pdf style=statdoc file='c:\samplereport.pdf' notoc style=statdoc;
  title 'Sample Report';
proc report data=test.test  nowd headline ;
define manager / order order=formatted;
run;
          ods pdf close;
          ods listing;


/*After testing it, found a page contains 29 lines*/


proc means data=test.test nway noprint;
 class manager;
 output out=count n=count;
run;
data result;
 merge test.test count(keep=manager count);
 by manager;
run;
data result;
 set result;
 mod=mod(count,29);
run;
data result; * To decide Page break point;
 set result;
 by manager;
 retain break 1;
 if last.manager then sum_mod+mod;
 if count +sum_mod ge 29 and manager ne lag(manager) then do;
                                                       break+1;
                                                       sum_mod=0;
                                                        end;
run; 


/*get the desired pdf */
ods listing close;
          options papersize=A4 nodate;
          options orientation=landscape leftmargin="0.2cm" rightmargin="0.2cm" topmargin="0.5cm" bottommargin="0.5cm";
          goptions ftext = "helvetica/bold" noborder device=CGMOF97P NOGRAPHRC;
          ods pdf style=statdoc file='c:\desired_report.pdf' notoc style=statdoc;
  title 'Desired Report';
proc report data=result  nowd headline ;
column break manager department sales ;
 define break /group noprint;
 define manager / order  order=formatted;
  break after break /page;
run;
          ods pdf close;
          ods listing;

Ksharp

Re: ODS PDF listing: Breaking up a table values by group in SAS

1239 — Fri, 14 Oct 2011 07:14:22 GMT

Thanks a lot Ksharp for the code. Its working as per the requirement.