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

 

 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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
Astounding
PROC Star

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
Super User

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
Obsidian | Level 7

that did the trick! 

 

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

ballardw
Super User

@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
Quartz | Level 8

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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