topic Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h in ODS and Base Reporting

Want to write header and trailer b4 and after using the line statement in the proc report Need help!

Inp — Sat, 29 Jul 2017 20:04:35 GMT

Hello,

See my below coding.

I am trying to create a pdf report. , I want to write 12 lines of customer information only at the page 1 ( in the below program I added only 2 lines at Compute before _page_) then want to continues with the table contained detail informaion, then at last I want to write the summary information( in the below program add two lines at Computer after _page_) , In the below program it create header and trailer at all pages. (Note: I don't want to use the title and foot note) . It doesnt create the report the way I want.

DATA TESTDATA;
FORMAT TESTDATE DATE9.;
DO I = 1 TO 500;
NAME = 'TESTNAME1'||PUT(I,Z3.);
ACCOUNT=I;
AMOUNT=I*300.23;
TESTDATE ='22JAN2015'D + I ;
OUTPUT;
END;
RUN;

data TESTDATA;
SET TESTDATA end=eof;
pagex = INT(_N_/15) + 1;
if eof then
call symput('Lastpage',pagex);
RUN;

ods _all_ close;;
ods pdf file = 'C:\TMP\TEST.PDF' NOTOC;
proc report data=testdata NOWD ls=100 ps=20 OUT=TEMP;
BY pagex;
column NAME ACCOUNT TESTDATE AMOUNT PAGEX;
DEFINE PAGEX / NOPRINT GROUP;
DEFINE NAME / 'CUSTOMER NAME';
DEFINE ACCOUNT / 'ACCOUNT NUMBER';
DEFINE TESTDATE / 'START DATE';
DEFINE AMOUNT/ 'AMOUNT PAID';
BREAK AFTER PAGEX / PAGE;
compute before _page_/ left;
If pagex = 1 then do;
line ' this is header line';
line 'I need to wirte this at the page 1 only';
end;
endcomp;
compute after _page_/ left;
if pagex=&lastpage then do;
line 'This is trailer line';
line 'I need to wirte this at the very last page (not every page)';
end;
endcomp;
RUN ;

ods pdf close;
ods listing;

Any help is much appriciated.

thanks

Tony

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Inp — Sat, 29 Jul 2017 17:41:23 GMT

I forget mention.

I am using Winodws SAS 9.2 TS2M3

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Tom — Sat, 29 Jul 2017 19:11:57 GMT

Why did you list PAGEX last on the COLUMN statement? You probably want to list that variable FIRST.

Then you can use COMPUTE BEFORE PAGEX to print on the first page.

Also use the Insert SAS code icon n the editor when you want to paste in sample code.

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Cynthia_sas — Sat, 29 Jul 2017 21:33:21 GMT

Hi:

This will NOT work for you:

If pagex = 1 then do;
line ' this is header line';
line 'I need to wirte this at the page 1 only';
end;

because PROC REPORT does NOT write a LINE statement conditionally -- PROC REPORT treats the LINE statement differently than the DATA step and the LINE statement is ALWAYS executed. The only thing you can control is the length of the line you write and WHEN the length is 0, then PROC REPORT suppresses the LINE output. See this Tech Support note: http://support.sas.com/kb/37/763.html and see the explanation on page 9 of this paper https://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf .

Here is a proof of concept.

cynthia


data newshoes;
  set sashelp.shoes end=eof;
  where region in ('Asia' 'Canada');
  pagex = INT(_N_/15) + 1;
  if eof then
    call symput('Lastpage',pagex);
run;

 
options nodate number pageno=1;
ods pdf file='c:\temp\proof_of_concept1.pdf';
  
  proc report data=newshoes;
    column pagex region subsidiary sales;
	define pagex / group /*noprint*/;
	define region / order;
	define subsidiary / order;
	define sales / sum;
	break after pagex / page;
    compute before _page_;
       holdpg = pagex;
       if pagex=1 then do;
          l1 = 'Write this one time';
          l2 = 'Before everything';
	  lg1 = length(l1);
	  lg2 = length(l2);
       end;
       else do;
          l1=' ';
          l2 = ' ';
	  lg1=0;
	  lg2=0;
       end;
	   line l1 $varying. lg1;
           line l2 $varying. lg2;
     endcomp;
     compute after _page_;
       if holdpg = &lastpage then do;
          l1 = 'Write this one time';
          l2 = 'After everything';
	  lg1 = length(l1);
	  lg2 = length(l2);
       end;
       else do;
          l1=' ';
          l2 = ' ';
	  lg1=0;
	  lg2=0;
       end;
	   line l1 $varying. lg1;
           line l2 $varying. lg2;
   endcomp;
run;
ods pdf close;

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Inp — Mon, 31 Jul 2017 15:06:47 GMT

Hi Cynthia,

thanks very much. your solution was perfect for what I expect. Thank you so much. I have still one more question.

I want to summarize four more numeric variable in my case. when I put rbreak after / summarize to summarize all the numeric values, summarizing comes in the next page.

In your example , I have added the Rbreak statment and I found it comes in a separte page , instead of comming on the same page?Is there any simple way to fix it?

Again thanks very much Cythiia.

data newshoes;
set sashelp.shoes end=eof;
where region in ('Asia' 'Canada');
pagex = INT(_N_/15) + 1;
if eof then
call symput('Lastpage',pagex);
run;

ods listing close;
options nodate number pageno=1;
ods pdf file='c:\tmp\proof_of_concept1.pdf';

proc report data=newshoes nowd;
column pagex region subsidiary sales;
define pagex / group /*noprint*/;
define region / order;
define subsidiary / order;
define sales / sum;
break after pagex / page;
compute before _page_;
holdpg = pagex;
if pagex=1 then do;
l1 = 'Write this one time';
l2 = 'Before everything';
lg1 = length(l1);
lg2 = length(l2);
end;
else do;
l1=' ';
l2 = ' ';
lg1=0;
lg2=0;
end;
line l1 $varying. lg1;
line l2 $varying. lg2;
endcomp;
compute after _page_;
if holdpg = &lastpage then do;
l1 = 'Write this one time';
l2 = 'After everything';
lg1 = length(l1);
lg2 = length(l2);
end;
else do;
l1=' ';
l2 = ' ';
lg1=0;
lg2=0;
end;
line l1 $varying. lg1;
line l2 $varying. lg2;
endcomp;

rbreak after / summarize;
run;
ods pdf close;
ods listing;

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Cynthia_sas — Wed, 02 Aug 2017 23:10:02 GMT

Hi:

I don't think you're going to be able to do this with one pass through the data. However, one of the nice features of PROC REPORT is that it WILL create an OUT=dataset. So code like this will "presummarize" your data and will also make a new dataset called POC2:


ods html file='c:\temp\getdataready.html';
data newshoes;
  set sashelp.shoes end=eof;
  where region in ('Asia' 'Canada');
  pagex = INT(_N_/15) + 1;
  if eof then
    call symput('Lastpage',pagex);
run;

   
  proc report data=newshoes out=poc;
    column pagex region subsidiary sales;
	define pagex / group /*noprint*/;
	define region / order;
	define subsidiary / order;
	define sales / sum;
	rbreak after / summarize;
run;

data poc2;
  set poc;
  ord = _n_;
  if _break_ = '_RBREAK_' then do;
     pagex=4;
	 region = 'Grand Total';
  end;
run;
proc print data=poc2;
  title 'After pre-summarizing';
run;
title;
ods html close;

Then you use POC2 data, which now has the Grand Total line as part of pagex=4 (in my data). Next, to get this output:

Notice how your Grand Total line is "inside" the group for PAGEX and the code you submit is mostly the same as previously shown before except for the fact that since the data is pre-summarized, we can use ORDER instead of GROUP for the usage items. I did not put NOPRINT on PAGEX or ORD variables because I wanted you to see how POC2 was different from the original report.


options nodate number pageno=1;
ods pdf file='c:\temp\proof_of_concept_presummarize2.pdf';

proc report data=poc2 missing;
    column pagex ord region subsidiary sales;
	define pagex / order /*noprint*/;
	define region / order;
	define subsidiary / order;
	define sales / sum;
	break after pagex / page;
    compute before _page_;
	   holdpg = pagex;
       if pagex=1 then do;
          l1 = 'Write this one time';
          l2 = 'Before everything  ';
          lg1 = length(l1);
          lg2 = length(l2);
       end;
       else do;
          l1=' ';
          l2 = ' ';
          lg1=0;
          lg2=0;
       end;;
	   line l1 $varying. lg1;
       line l2 $varying. lg2;
	endcomp;
	compute after _page_;
       if holdpg = &lastpage then do;
          l1 = 'Write this one time';
          l2 = 'After everything';
          lg1 = length(l1);
          lg2 = length(l2);
       end;
       else do;
          l1=' ';
          l2 = ' ';
          lg1=0;
          lg2=0;
      end;
      line l1 $varying. lg1;
      line l2 $varying. lg2;
endcomp;
run;
ods pdf close;

cynthia

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Inp — Mon, 07 Aug 2017 19:28:47 GMT

HI Cynthia,

Thanks very much for giving me a perfect solution to my problem.

Here is the story, We have been sending hard copy report every month to our clients , We decided to stop hard copy report and send the soft copy to customers where it will save money and time. Some customers expect both the excel and pdf report, I was able to create xls report, but when I create the PDF reportt, I have some little chalanges, Thanks for your help to achive this so successfully.

I don't know if you remember me, I came to your presentation at the SAS Global Forum in 2017 Orlando and it was amazing and I learned a lot about advance reporting from your presentaion. I chart with you after the presentaion at right behind the Presentation room, and got many tips and information . Thanks very much for your tips and many informaiton you shared with me.

Thanks SAS Global Forum for giving such opportunity.

Thanks very much Cynthia and god bless you and your family.

Regards.

Inp

Re: Want to write header and trailer b4 and after using the line statement in the proc report Need h

Cynthia_sas — Mon, 07 Aug 2017 22:36:07 GMT

Hi!
Yes, I remember that conversation! Glad to see that you're still working with PROC REPORT!
cynthia