Subtotal on a proc tabulate

[robm]

 I have a macro with 3 proc tab statements one with 1 row param one with 2 row params and one with 3 row params 

what i want on all of then is for there to be a row subtotal after the &pr param 

I tried 

 class &pr*(Total)  &sr &tr &col; but that didnt work ...any suggestions

%macro tab;
%if &sr = niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ') all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr" };
%end;
%else %if &tr = niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &sr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ')*(&sr = ' ' ) all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr / &sr" };
%end;
%else %if &tr ^= niu and &sr ^= niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &sr &tr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ')*(&sr = ' ' )*(&tr = ' ' ) all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr / &sr / &tr" };
%end;
%MEND TAB;
%tab;

[Astounding]

It looks like your requests for statistics are part of the COLUMN definitions.  If that's the case, you can't add other statistics (such as SUM) as part of the ROW definitions.  That would mean that a single cell has two statistics requested.

[ballardw · Accepted solution]

Did you get a version of this working before you started the macro stuff.

Since the behavior of ALL depends on where it appears, whether within ( ) with another variable then I suggest getting it working without macro variables. If you have difficulties then post some data and what you are expecting the tables to look like with that data.

Which variable do you want to subtotal when you have 2 or 3 row variables? See the code below to generate multiple sub totals and keep the one you want.

You do not need 3 separate calls to proc tabulate unless you are actually subsetting the data used with a WHERE or a variable  has different roles in different tables. That forces SAS to read the data 3 times. As long as any variable referenced in Class or Var statements are in the data set, whether you use them or not, then factoring out the common elements also makes it easier to read.

May be:

proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
      class &pr &sr &tr &col;
      var &mes;
      keylabel Sum = " ";

   %if &sr = niu %then %do;
      table (&pr = ' ') all='Total', 
      ((&col = ' ' all = 'Total')*((&measureexp)))/
      contents = ' ' misstext=' ' box={label="&pr" };
   %end;
   %else %if &tr = niu %then %do;
       table (&pr = ' ')*(&sr = ' ' All='Total for &PR level' ) all='Overall Total', 
       ((&col = ' ' all = 'Total')*((&measureexp)))/
       contents = ' ' misstext=' ' box={label="&pr / &sr" };
   %end;
   %else %if &tr ^= niu and &sr ^= niu %then %do;
      table (&pr = ' ')*(&sr = ' '  All='Total for &PR level' )*(&tr = ' ' All='Total for &sr level') all='Overall Total', 
      ((&col = ' ' all = 'Total')*((&measureexp)))/
      contents = ' ' misstext=' ' box={label="&pr / &sr / &tr" };
   %end;
run;

You may also want to investigate MULTILABEL formats for some purposes with Proc Tabulate. But again no data or example tables its hard to be more specific

[merrittr]

that did the trick! 

Thanks Ballard I just have to figure out how to mark this as the answer

[ballardw]

---

@merrittr wrote:

that did the trick! 

Thanks Ballard I just have to figure out how to mark this as the answer

---

It appears that you may have two identities on this forum. Only the creator of the thread, ROBM, can mark answers. So you would have to log in as Robm.

[robm]

aaagh dammit you are correct the other one is my "company account" that we have paid support for