Proc Tabulate - Total sum in last column

[Sathish_jammy]

Hi experts, 

I trying to sum up my analysis table using Proc Tabulate. Kindly gothrough the given sample method in detail. 

I want to sum up the Gender variable at the end of OP.

data have;
input Type$ Grp$ Sex$;
cards;
1 G F
1 G M
3 O F
3 P M
3 G F
4 P M
7 G M
7 G F
;

proc tabulate data = have;
class Type Grp Sex;
tables Type, Grp*Sex ALL;
keylabel n=' ';
run;

The result shares the perfect OP values but I required it in the right side displayed tabular format.

Please help me to sum up the Sex variable as the last column in the table. I tried a lot but nothing works well.

Thanks in advance!

[koyelghosh]

Will it matter if you achieve the same result with PROC REPORT instead of PROC TABULATE?

[Sathish_jammy]

No issues @koyelghosh , Kindly share the details.

Much Thanks!

[Ksharp]

data have;
input Type$ Grp$ Sex$;
cards;
1 G F
1 G M
3 O F
3 P M
3 G F
4 P M
7 G M
7 G F
;
proc freq data=have noprint;
table type*grp*sex/out=level sparse list;
run;
proc tabulate data = have classdata=level;
class Type Grp ;
class Sex/order=internal descending;
tables Type=' ', (Grp=' ' ALL)*Sex=' '/box='Type';
keylabel n=' ';
run;

[Sathish_jammy]

All 3 solutions work well. Thank you so much for your ideas.

Much thanks! @Ksharp  @ballardw  @koyelghosh 

[koyelghosh · Accepted solution]

Try this .. formatting is not exactly correct.. but it is close.

PROC REPORT DATA=have;
	COLUMNS Type (Grp , Sex) ('Total' Sex=Sex_Total);
	DEFINE Type / GROUP;
	DEFINE Grp / ACROSS;
	DEFINE Sex / ACROSS;
	DEFINE Sex_Total / ACROSS;
RUN;

The output is as below

Output

Let me know if this is what you wanted.

[ballardw]

Please consider:

data have;
input Type$ Grp$ Sex$;
cards;
1 G F
1 G M
3 O F
3 P M
3 G F
4 P M
7 G M
7 G F
;
proc sort data=have;
  by descending sex;
run;
proc tabulate data = have;
class Type Grp ;
class Sex /order=data;
tables Type, 
      (Grp all='Total')*Sex=' ' 
      /misstext=' '
;
keylabel n=' ';
run;

The ALL would as a minimum need to nest with SEX to get the desired totals. The way shown is one way to do that.

Second is to get order of categorical text values can be very problematic as the default order will be FORMATTED and you may have issues getting a SORT order for character values as needed. In this case we could use a sort to get the only "problem" variable Sex and use the Order=data option. More complex order combinations may not work with character values. You've been warned.