The SAS Output Delivery System and reporting techniques

Can't Print Group Total at the end of Group Variable using Proc Report?

Reply
Contributor
Posts: 21

Can't Print Group Total at the end of Group Variable using Proc Report?

Hi Everybody,

I am trying to print the group total observations at the end of group variable.
Here I have created a page variable for every 15 rows of a group. So, I need group totals only after the last page of a group variable.
But i am getting page by totals instead. I've got the solution in data _null_ but i am so curious to find out a solution using Proc Report and want to learn more about proc report.
Help will be really appreciated.
here is the code i've used.

DATA TEST;
INPUT @1 PID $14.
@16 LASTNAME $10.
@27 FIRSTNAME $10.
@39 MI $1.
@41 SEX $1.
@43 DEATHDATE MMDDYY10.
@54 TY $1.
@56 PCN $16.
@73 COUNTY 2.
@76 GROUPS 1.;
FORMAT DEATHDATE MMDDYY10.;
CARDS;
RECIPIENT1 LASTNAME1 FIRSTNAME1 S M 03/23/2011 K 1100000000000001 10 1
RECIPIENT2 LASTNAME2 FIRSTNAME2 A F 03/20/2011 K 1100000000000002 20 1
RECIPIENT3 LASTNAME3 FIRSTNAME3 C F 03/20/2011 K 1100000000000003 30 1
RECIPIENT4 LASTNAME4 FIRSTNAME4 B F 03/20/2011 K 1100000000000004 40 1
RECIPIENT5 LASTNAME5 FIRSTNAME5 N F 03/20/2011 K 1100000000000005 50 1
RECIPIENT6 LASTNAME6 FIRSTNAME6 K F 03/20/2011 K 1100000000000006 60 1
RECIPIENT7 LASTNAME7 FIRSTNAME7 A F 03/20/2011 K 1100000000000007 70 1
RECIPIENT8 LASTNAME8 FIRSTNAME8 R F 03/20/2011 K 1100000000000008 80 1
RECIPIENT9 LASTNAME9 FIRSTNAME9 A F 03/20/2011 K 1100000000000009 90 1
RECIPIENT10 LASTNAME10 FIRSTNAME10 A F 03/20/2011 K 1100000000000010 10 1
RECIPIENT11 LASTNAME11 FIRSTNAME11 A F 03/20/2011 K 1100000000000011 20 1
RECIPIENT12 LASTNAME12 FIRSTNAME12 G F 03/20/2011 K 1100000000000012 30 1
RECIPIENT13 LASTNAME13 FIRSTNAME13 A F 03/20/2011 K 1100000000000013 40 1
RECIPIENT14 LASTNAME14 FIRSTNAME14 J F 03/20/2011 K 1100000000000014 50 1
RECIPIENT15 LASTNAME15 FIRSTNAME15 A F 03/20/2011 K 1100000000000015 60 1
RECIPIENT16 LASTNAME16 FIRSTNAME16 E F 03/20/2011 K 1100000000000016 70 1
RECIPIENT17 LASTNAME17 FIRSTNAME17 A F 03/20/2011 K 1100000000000017 80 1
RECIPIENT18 LASTNAME18 FIRSTNAME18 L F 03/20/2011 K 1100000000000018 90 1
RECIPIENT19 LASTNAME19 FIRSTNAME19 A F 03/20/2011 K 1100000000000019 10 1
RECIPIENT20 LASTNAME20 FIRSTNAME20 W F 03/20/2011 K 1100000000000020 20 1
RECIPIENT21 LASTNAME21 FIRSTNAME21 G M 03/21/2011 K 1100000000000021 30 2
RECIPIENT22 LASTNAME22 FIRSTNAME22 R F 03/22/2011 K 1100000000000023 40 2
RECIPIENT23 LASTNAME23 FIRSTNAME23 E M 03/21/2011 K 1100000000000023 50 3
RECIPIENT24 LASTNAME24 FIRSTNAME24 T M 03/21/2011 K 1100000000000024 60 3
;
RUN;

PROC SORT DATA=TEST OUT=TEST1;
BY GROUPS COUNTY PID;
RUN;

DATA FINAL;
SET TEST1;
BY GROUPS COUNTY PID;
RETAIN PAGEIT 0 TOTAL 1;
COUNTER+1;
IF FIRST.GROUPS OR COUNTER > 15 THEN
DO;
PAGEIT+1;
COUNTER=1;
END;
DROP COUNTER;
RUN;

PROC TEMPLATE;
DEFINE STYLE Custom;
parent=styles.printer;
style batch from batch /
font_face='Courier New'
font_size=10pt;
END;
RUN;

OPTIONS NODATE NONUMBER NOBYLINE ORIENTATION=LANDSCAPE ;
ODS ESCAPECHAR = "~";
/*ODS PDF FILE = "&REPORT_PATH&PDF_FILENAME" COMPRESS=0 NOTOC STYLE=Custom; */
ODS PDF FILE = "/xxxx/abcd/SAMS.pdf" COMPRESS=0 NOTOC STYLE=Custom;

PROC REPORT DATA=FINAL NOWD MISSING SPLIT="*"
STYLE(HEADER)={BACKGROUND=_UNDEF_ FONT_FACE= "Courier New" FONT_SIZE=10pt}
STYLE(REPORT)={RULES=GROUPS FRAME=VOID FONT_FACE= "Courier New" FONT_SIZE=10pt }
STYLE(COLUMN)={BACKGROUND=WHITE FOREGROUND=black FONT_FACE= "Courier New" FONT_SIZE=10pt};

COLUMN GROUPS PID LASTNAME FIRSTNAME MI SEX DEATHDATE TY PCN COUNTY PAGEIT TOTAL;
BY PAGEIT;

DEFINE PID /DISPLAY 'RECIPIENT ID' STYLE={JUST=LEFT CELLWIDTH=35MM};
DEFINE LASTNAME/DISPLAY 'LAST NAME' STYLE={JUST=LEFT CELLWIDTH=40MM};
DEFINE FIRSTNAME/DISPLAY 'FIRST NAME' STYLE={JUST=LEFT CELLWIDTH=40MM};
DEFINE MI/DISPLAY 'MI' STYLE={JUST=LEFT CELLWIDTH=15MM};
DEFINE SEX/DISPLAY 'SEX' STYLE={JUST=LEFT CELLWIDTH=15MM};
DEFINE DEATHDATE/DISPLAY 'DEATH DATE' FORMAT=MMDDYY10. STYLE={JUST=LEFT CELLWIDTH=25MM};
DEFINE TY/DISPLAY 'TY' STYLE={JUST=LEFT CELLWIDTH=15MM};
DEFINE PCN/DISPLAY 'TCN' STYLE={JUST=LEFT CELLWIDTH=40MM};
DEFINE COUNTY/DISPLAY 'COUNTY' STYLE={JUST=LEFT CELLWIDTH=35MM};
DEFINE GROUPS/GROUP NOPRINT;
DEFINE PAGEIT/ORDER NOPRINT;
DEFINE TOTAL/ANALYSIS NOPRINT;

COMPUTE AFTER PAYER/STYLE={ASIS=ON FONT_FACE= "Courier New" FONT_SIZE=10pt};
LINE @1 "TOTAL CLAIMS REPORTED" TOTAL.SUM COMMA6.;
ENDCOMP;

COMPUTE BEFORE _PAGE_ /STYLE={ASIS=ON FONT_FACE= "Courier New" FONT_SIZE=10pt};
LINE @1 "&RTTL2" @7 ":" @9 "&RTTL1" @29 " &RTTL3~{nbspace 9}&RTTL4 : &RUNDATE" ;
LINE @1 "&RTTL5" @7 ":" @9 PAYER 5. @52 " &RTTL6~{nbspace 32}&RTTL7 : &RUNTIME" ;
LINE @92 " &RTTL8~{nbspace 9}: ~{THISPAGE}" ;
LINE @44 "&REPORT_TITLE";
LINE @48 "AS OF &ASOFDT";
LINE "~{newline 2}";
ENDCOMP;

RUN;
ODS PDF TEXT = "~S={JUST=C FONT_FACE='Courier New' FONT_SIZE=10pt } **** END OF REPORT ****";
ODS PDF CLOSE;

Thanks & Regards
Aravind
SAS Super FREQ
Posts: 8,862

Re: Can't Print Group Total at the end of Group Variable using Proc Report?

Hi:
This is what I do when I need arbitrary page breaks based on some variable. In this instance, the PGBRK variable is set to put only 5 obs on every page. Then my grouping variable is SEX. At the end of each value, M or F, a summary is done.

cynthia
[pre]
proc sort data=sashelp.class;
by sex;
run;

data class;
set sashelp.class; by sex;
retain pgbrk 0;
cntr+1;
if first.sex then do; pgbrk = 0; cntr=1; end;
pgbrk = ceil(divide(cntr,5));
output;
drop cntr;
run;

ods listing;
proc print data=class;
run;

ods listing close;
ods pdf file='c:\temp\two_brk.pdf';

proc report data=class nowd;
title 'it is silly to add up height and weight and age, but shows the point';
column sex pgbrk name age height weight;
define sex / order;
define pgbrk / order;
define age / sum;
define weight / sum;
define height/sum;
break before pgbrk / page;
break before sex / page;
break after sex/summarize;
run;

ods pdf close;
[/pre]
Contributor
Posts: 21

Re: Can't Print Group Total at the end of Group Variable using Proc Report?

Posted in reply to Cynthia_sas
Hi Cythia,

If you look at the code and sample data of mine, then you see lot of variables with display option and i am not printing group variable in the report and i dont have any numeric variables that i need to print in the report.
I just need to count the number of observations for each group and should print that number at the end of each page such as Group Total: 1234.

The final report should look like this

GROUP:1 PAGE:1
PID LASTNAME FIRSTNAME MI SEX DEATHDATE TY PCN COUNTY
1234 TEST SAMPLE S F xx/xx/xxxx R 1005 030
-------------------------------------------------------------------------------------------------------
will have 15 observations in first page for example


GROUP:1 PAGE:2
PID LASTNAME FIRSTNAME MI SEX DEATHDATE TY PCN COUNTY
1234 TEST SAMPLE S F xx/xx/xxxx R 1005 030
-------------------------------------------------------------------------------------------------------
the reamaing obserations in GROUP 1

Group Total : xxxxx


Thanks & Regards
Aravind
SAS Super FREQ
Posts: 8,862

Re: Can't Print Group Total at the end of Group Variable using Proc Report?

Hi:
I did look at your code, but it had ERROR and WARNING messages:
[pre]
207 COMPUTE BEFORE _PAGE_ /STYLE={ASIS=ON FONT_FACE= "Courier New" FONT_SIZE=10pt};
208 LINE @1 "&RTTL2" @7 ":" @9 "&RTTL1" @29 " &RTTL3~{nbspace 9}&RTTL4 : &RUNDATE" ;
WARNING: Apparent symbolic reference RTTL2 not resolved.
WARNING: Apparent symbolic reference RTTL1 not resolved.
WARNING: Apparent symbolic reference RTTL3 not resolved.
WARNING: Apparent symbolic reference RTTL4 not resolved.
WARNING: Apparent symbolic reference RUNDATE not resolved.
209 LINE @1 "&RTTL5" @7 ":" @9 PAYER 5. @52 " &RTTL6~{nbspace 32}&RTTL7 : &RUNTIME" ;
WARNING: Apparent symbolic reference RTTL5 not resolved.
WARNING: Apparent symbolic reference RTTL6 not resolved.
WARNING: Apparent symbolic reference RTTL7 not resolved.
WARNING: Apparent symbolic reference RUNTIME not resolved.
210 LINE @92 " &RTTL8~{nbspace 9}: ~{THISPAGE}" ;
WARNING: Apparent symbolic reference RTTL8 not resolved.
211 LINE @44 "&REPORT_TITLE";
WARNING: Apparent symbolic reference REPORT_TITLE not resolved.
212 LINE @48 "AS OF &ASOFDT";
WARNING: Apparent symbolic reference ASOFDT not resolved.
213 LINE "~{newline 2}";
214 ENDCOMP;
215
216 RUN;

NOTE: Groups are not created because the usage of PID is DISPLAY. To avoid this note, change all GROUP variables to ORDER
variables.
ERROR: The BREAK variable PAYER is not one of the GROUP or
ORDER variables.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.14 seconds
cpu time 0.14 seconds

[/pre]

So that's why I used a different example.

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 221 views
  • 0 likes
  • 2 in conversation