Sum rows in a table to produce new rows

[kelly6]

Hello, 

I'm a novice so apologies in advance - 

I have programmed a table, and now want to add two new rows - sum rows A and B into a new row, A+B; sum rows C and D into a new row, C+D. Is there a simple way to do this?

Enrollment		Phase 1	Phase 1	Phase 2	Phase 2
Site Name	Enrolled	#	%	#	%
A	8	8	100	8	100
B	8	8	100	8	100
C	3	3	100	3	100
D	8	8	100	8	100
A+B
C+D
Total	27	27	100	27	100

proc report data = Samples;
column ('Enrollment' sitename Enrolled ('Sample Collection' ('Phase 1' NP1 PctNP1) ('Phase 2' NP2 PctNP2)));
define sitename / group center format = sites.;
define Enrolled / analysis '#';
define NP1 / analysis '#';
define PctNP1 / computed '%';
define NP2 / analysis '#';
define PctNP2 / computed '%';
define NP2 / analysis '#';
compute PctNP1;
PctNP1 = round((_c3_/_c2_)*100,0.1);
endcomp;
compute PctNP2;
PctNP2 = round((_c5_/_c2_)*100,0.1);
endcomp;
compute sitename;
if _break_ ='_RBREAK_' then
call define('sitename','style','style=[pretext="Total"]'); 
endcomp;
rbreak after / summarize ul ol;
run;

ods startpage = now;

[PaigeMiller]

It would help if you provide sample data that we can work with, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Does the final output have to be a table in an output destination, or can it be a SAS data set, or something else?

What is wrong with the code you provided? You don't really say.

[FreelanceReinh]

Hello @kelly6 and welcome to the SAS Support Communities!

This is a typical use case of a multilabel format. See Example 12: Using Multilabel Formats in the PROC REPORT documentation for a very similar application. So, all you need is

1. a PROC FORMAT step creating a variant of your sites. format (say, sitesml.) analogous to format agelfmt in the documentation example
2. to specify this format in the DEFINE statement for variable sitename along with the three additional options mlf order=data preloadfmt.

[ErikLund_Jensen]

Hi @kelly6 

There is no way for SAS to guess which rows you want included in a sub-summation. You need a grouping variable in each row to tell which sum-group it belongs to, eg A 1, B 1, C 2, D 2.