topic Re: How to add total row at bottom of table by group in SAS Programming

How to add total row at bottom of table by group

Merdock — Wed, 26 Apr 2023 18:03:02 GMT

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!

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

PaigeMiller — Wed, 26 Apr 2023 18:51:46 GMT

Is your question:

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?)

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

Merdock — Wed, 26 Apr 2023 18:56:51 GMT

@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!

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

PaigeMiller — Wed, 26 Apr 2023 19:14:18 GMT

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;

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

Merdock — Wed, 26 Apr 2023 20:58:02 GMT

this works perfectly, thank you so much!