Quartz | Level 8

Subtotal and Grand Total in dataset

Hi,

I have this dataset where I want to display the subtotals and grand total without using a proc tabulate.

From my "HAVE" dataset, I'd like the totals (num1 and num2) be calculated for each Group, and the grand total be calculated, placed in the first cell of the "WANT" dataset. I'd like to have the output in SAS dataset form, not Proc Report nor Tabulate. Thank you!

data have;
input Group :\$20. Member :\$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

data want;
input final_text :\$20. num1 num2;
datalines;
Grand_Total 19 5
Group_a 10 1
Member_a_1 6 0
Member_a_2 4 1
Group_b 9 4
Member_b_1 6 3
Member_b_2 3 1
;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Subtotal and Grand Total in dataset

Open want dataset.

data have;
input Group :\$20. Member :\$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

ods select none;
proc report data=have out=want nowd;
column  Group  Member  num1 num2;
define group/group;
compute before;
Member='Grand_Total';
endcomp;
compute before group;
Member=group;
endcomp;
break before group/summarize;
rbreak before/summarize;
run;
ods select all;
5 REPLIES 5
Tourmaline | Level 20

Re: Subtotal and Grand Total in dataset

Are you allowed to use PROC SQL?

Quartz | Level 8

Yes.

Super User

Re: Subtotal and Grand Total in dataset

Data step language is good for this:

data have;
input Group :\$20. Member :\$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

data want;
set have end=eof;
by group;
retain
sum_num1
sum_num2
grand_num1 0
grand_num2 0
;
if first.group
then do;
sum_num1 = 0;
sum_num2 = 0;
end;
grand_num1 + num1;
grand_num2 + num2;
sum_num1 + num1;
sum_num2 + num2;
output;
if last.group
then do;
member = 'Group Sum';
num1 = sum_num1;
num2 = sum_num2;
output;
end;
if eof
then do;
member = 'Grand Total';
num1 = grand_num1;
num2 = grand_num2;
output;
end;
drop sum: grand:;
run;
Quartz | Level 8

Re: Subtotal and Grand Total in dataset

thank you! It's also a good solution

Super User

Re: Subtotal and Grand Total in dataset

Open want dataset.

data have;
input Group :\$20. Member :\$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

ods select none;
proc report data=have out=want nowd;
column  Group  Member  num1 num2;
define group/group;
compute before;
Member='Grand_Total';
endcomp;
compute before group;
Member=group;
endcomp;
break before group/summarize;
rbreak before/summarize;
run;
ods select all;
Discussion stats
• 5 replies
• 3488 views
• 0 likes
• 4 in conversation