Quartz | Level 8

How to add total row at bottom of table by group

Hello,

I have the following dataset and I was wondering how can I add a row at the bottom with the totals?

``````data have;
input VISIT SITE\$ N EXPCT OCCRD PERCENT OCCPCT\$;
datalines;
1	AAA	11	10	7	70	7(70%)
2	AAA	11	0	0	0	0(0%)
1	BBB	14	5	5	100	5(100%)
2	BBB	14	4	2	50	2(50%)
1	CCC	80	46	39	84.783	39(84.8%)
2	CCC	80	0	0	0	0(0%)
;
run;
proc print data=have; run;
``````

I want my final output (in Word) to look like this:

Where the percentage in the Total row comes from division by the Total Site N so: 48.6%=51/105, and 1.9%=2/105.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

Re: How to add total row at bottom of table by group

This should work. I consider something like the text string 7(70%) to be such a poor way to display data that I haven't programmed it. Of course, you can program it if you'd like. I have also fixed a spelling error.

``````proc summary data=have nway;
class visit;
var n expct occrd;
output out=_stats_ sum=;
run;

data have1;
length site \$ 5;
set have _stats_(drop=_:);
if missing(site) then site='Total';
if site='Total' then percent=occrd/n;
else percent=percent/100;
run;

proc report data=have1;
columns site n visit,(occrd percent expct);
define site/group "Site" order=data;
define n/group "N";
define visit/across "Yearly Visits";
define occrd/sum "Total Occurred";
define percent/sum "Percent" format=percent8.1;
define expct/sum "Total Expected";
run;
``````
--
Paige Miller
4 REPLIES 4
Diamond | Level 26

Re: How to add total row at bottom of table by group

How to produce the report shown?

Or, is it as you have stated, how to produce a data set that looks somewhat like the report? (If so, why?)

--
Paige Miller
Quartz | Level 8

Re: How to add total row at bottom of table by group

@PaigeMiller, the question is how to obtain the report shown based on the dataset have (specifically how to add that final row with Totals at the bottom). Thanks!

Diamond | Level 26

Re: How to add total row at bottom of table by group

This should work. I consider something like the text string 7(70%) to be such a poor way to display data that I haven't programmed it. Of course, you can program it if you'd like. I have also fixed a spelling error.

``````proc summary data=have nway;
class visit;
var n expct occrd;
output out=_stats_ sum=;
run;

data have1;
length site \$ 5;
set have _stats_(drop=_:);
if missing(site) then site='Total';
if site='Total' then percent=occrd/n;
else percent=percent/100;
run;

proc report data=have1;
columns site n visit,(occrd percent expct);
define site/group "Site" order=data;
define n/group "N";
define visit/across "Yearly Visits";
define occrd/sum "Total Occurred";
define percent/sum "Percent" format=percent8.1;
define expct/sum "Total Expected";
run;
``````
--
Paige Miller
Quartz | Level 8

Re: How to add total row at bottom of table by group

this works perfectly, thank you so much!
Discussion stats
• 4 replies
• 747 views
• 1 like
• 2 in conversation