topic Re: Proc Tabulate - Total sum in last column in SAS Procedures

Proc Tabulate - Total sum in last column

Sathish_jammy — Thu, 27 Jun 2019 07:03:52 GMT

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!

Re: Proc Tabulate - Total sum in last column

koyelghosh — Thu, 27 Jun 2019 11:03:27 GMT

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

Re: Proc Tabulate - Total sum in last column

Sathish_jammy — Thu, 27 Jun 2019 12:09:53 GMT

No issues @koyelghosh , Kindly share the details.

Much Thanks!

Re: Proc Tabulate - Total sum in last column

Ksharp — Thu, 27 Jun 2019 12:22:56 GMT

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;

Re: Proc Tabulate - Total sum in last column

koyelghosh — Thu, 27 Jun 2019 12:49:12 GMT

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.

Re: Proc Tabulate - Total sum in last column

ballardw — Thu, 27 Jun 2019 17:48:16 GMT

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.

Re: Proc Tabulate - Total sum in last column

Sathish_jammy — Fri, 28 Jun 2019 05:57:49 GMT

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

Much thanks! @Ksharp @ballardw @koyelghosh