Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

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

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;


Accepted Solutions
Solution
‎10-12-2011 11:54 PM
Super User
Posts: 9,676

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

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

View solution in original post


All Replies
Valued Guide
Posts: 2,174

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

has REPORT the equivalent of PROC PRINT feature PAGEBY?    

Super User
Super User
Posts: 6,499

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

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.

Contributor
Posts: 55

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

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

Super User
Posts: 9,676

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

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

Contributor
Posts: 55

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

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;

Super User
Super User
Posts: 6,499

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

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.

Respected Advisor
Posts: 3,777

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

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;

Solution
‎10-12-2011 11:54 PM
Super User
Posts: 9,676

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

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

Contributor
Posts: 55

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 359 views
  • 0 likes
  • 5 in conversation