topic Re: Sum rows in a table to produce new rows in SAS Programming

Sum rows in a table to produce new rows

kelly6 — Sat, 31 Oct 2020 03:25:00 GMT

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;

Re: Sum rows in a table to produce new rows

PaigeMiller — Sat, 31 Oct 2020 10:42:50 GMT

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.

Re: Sum rows in a table to produce new rows

FreelanceReinh — Sat, 31 Oct 2020 10:59:33 GMT

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

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

Re: Sum rows in a table to produce new rows

ErikLund_Jensen — Sat, 31 Oct 2020 11:01:58 GMT

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.