BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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
Ksharp
Super User

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;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

Are you allowed to use PROC SQL?

Kurt_Bremser
Super User

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;
angeliquec
Quartz | Level 8

thank you! It's also a good solution

Ksharp
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3540 views
  • 0 likes
  • 4 in conversation