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

Subtotals and Grand Total in dataset with transpose

Hi, I'd like to request assistance again in getting the subtotals and totals from this dataset, also transposing the variable "class" from long to wide. Let's say I would not have any idea how many distinct values of "class" will there be.

My preferred result would be a SAS dataset, in "WANT2".

Thank you!

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

data want2;
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 9 3
Member_b_2 0 1
;
run;
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Subtotals and Grand Total in dataset with transpose

``````

data have2;
input Group_Name :\$20. Member :\$20. class \$4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;
proc summary data=have2 nway;
class  Group_Name  Member class;
var num;
output out=temp(drop=_:) sum=;
run;
proc transpose data=temp out=temp1(drop=_:);
by Group_Name  Member;
id class;
var num;
run;

ods select none;
proc report data=temp1 out=want nowd;
column  Group_Name  Member  num1 num2;
define Group_Name/order;
compute before;
Member='Grand_Total';
endcomp;
compute before Group_Name;
Member=Group_Name;
endcomp;
break before Group_Name/summarize;
rbreak before/summarize;
run;
ods select all;``````
4 REPLIES 4
Super User

Re: Subtotals and Grand Total in dataset with transpose

Do you want a data set or a displayed report?
Otherwise use PROC MEANS with a CLASS statement to get the sun totals and totals. Put the grouping variables in the CLASS statement and variables you want summarized in the VAR statement.
Quartz | Level 8

Re: Subtotals and Grand Total in dataset with transpose

Hi Reeza. I'd like to have a dataset. Thank you!

Barite | Level 11

Re: Subtotals and Grand Total in dataset with transpose

Did not get your requirements completely, see if this helps:

``````data have2;
input Group_Name :\$20. Member :\$20. class \$4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;

proc means data=have2 nway noprint n ;
class Group_Name Member;
var num;
ways 0 1 2;
output out=want(drop=_type_ _freq_ _way_)  sum=/ ways;
run;``````
Super User

Re: Subtotals and Grand Total in dataset with transpose

``````

data have2;
input Group_Name :\$20. Member :\$20. class \$4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;
proc summary data=have2 nway;
class  Group_Name  Member class;
var num;
output out=temp(drop=_:) sum=;
run;
proc transpose data=temp out=temp1(drop=_:);
by Group_Name  Member;
id class;
var num;
run;

ods select none;
proc report data=temp1 out=want nowd;
column  Group_Name  Member  num1 num2;
define Group_Name/order;
compute before;
Member='Grand_Total';
endcomp;
compute before Group_Name;
Member=Group_Name;
endcomp;
break before Group_Name/summarize;
rbreak before/summarize;
run;
ods select all;``````
Discussion stats
• 4 replies
• 2114 views
• 0 likes
• 4 in conversation