topic Re: Can't Print Group Total at the end of Group Variable using Proc Report? in ODS and Base Reporting

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

aravind — Wed, 06 Apr 2011 14:27:03 GMT

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

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

Cynthia_sas — Wed, 06 Apr 2011 14:58:25 GMT

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]

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

aravind — Wed, 06 Apr 2011 15:53:23 GMT

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

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

Cynthia_sas — Thu, 07 Apr 2011 18:01:18 GMT

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