Help using Base SAS procedures

Subtotal on a proc tabulate

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Subtotal on a proc tabulate

 

 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;


Accepted Solutions
Solution
‎06-26-2017 01:51 PM
Super User
Posts: 11,343

Re: Subtotal on a proc tabulate

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 5,498

Re: Subtotal on a proc tabulate

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.

Solution
‎06-26-2017 01:51 PM
Super User
Posts: 11,343

Re: Subtotal on a proc tabulate

[ Edited ]

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

Contributor
Posts: 24

Re: Subtotal on a proc tabulate

that did the trick! 

 

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

Super User
Posts: 11,343

Re: Subtotal on a proc tabulate


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.

Frequent Contributor
Posts: 84

Re: Subtotal on a proc tabulate

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 308 views
  • 1 like
  • 4 in conversation