Proc tabulate using "ALL"

[Anita_n]

Dear all, 

if I have this code:

proc tabulate data=mydata format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class var1 var2 var3 / order=data style=[textalign=center ] missing;
  classlev var1 var2 var3 / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
   var  Num_of_cases total_cases percentage /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
   keyword all sum  /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];

  table ( var1=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) all='Totals per line')*
 (num_of_cases='Number of cases' total_cases='sum of all cases' percentage='Percentages'*f=percent8.2) , 
   (sum=' '* var2=''*var3=' ')  / row=float nocontinued indent=1 rts=2  misstext=[label=" " ] 
   style=[cellpadding=3 font_size=5pt foreground=dimgray] 
   box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;

If I use the "All=Totals per line" statement, I get the sum totals of all these three variables: num_of_cases, total_cases and percentage 

at the bottom of the table. But I only need the sum totals of num_of_cases. Please help

and

[yabwon]

Share some example data so we could test your code and help...

From what you shown my best guess would be to do something like: 

proc tabulate data = sashelp.cars;
  class origin cylinders;
  var invoice mpg_city;
  table (cylinders="Cylinder Inv." all="Tot. Inv.")*invoice=" "*sum=" "*f=dollar15.2 
         cylinders="Cylinder MPG"*mpg_city=" "*sum=" "
        ,origin
        / row=float
        ;
run;

Bart

[Anita_n]

@yabwon : using sashelp.cars as a sample data. I only want to output the totals per country only for the cylinders(at the bottom)

proc tabulate data=sashelp.cars format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class make type origin / order=data style=[textalign=center ] missing;
  classlev make type origin / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
   var  cylinders horsepower MSRP /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
   keyword all sum  /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];

  table ( make=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) all='Totals per country')*
 (cylinders='cylinders' horsepower='horsepower' msrp='msrp') , 
   (sum=' '* type=''*origin=' ')  / row=float nocontinued indent=1 rts=2  misstext=[label=" " ] 
   style=[cellpadding=3 font_size=5pt foreground=dimgray] 
   box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;

[yabwon]

I guess something like this:

proc tabulate data=sashelp.cars format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class make type origin / order=data style=[textalign=center ] missing;
  classlev make type origin / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
   var  cylinders horsepower MSRP /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
   keyword all sum  /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];

  table ( make=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) /*all='Totals per country'*/ )*
 (cylinders='cylinders' horsepower='horsepower' msrp='msrp') 
  cylinders='cylinders'*all='Totals per country' /* <- new code */, 
   (sum=' '* type=''*origin=' ')  / row=float nocontinued indent=1 rts=2  misstext=[label=" " ] 
   style=[cellpadding=3 font_size=5pt foreground=dimgray] 
   box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;

?

Bart

[ballardw · Accepted solution]

Suggestion: Unless your question relates to actual use of style overrides and cell appearance for questions on the forum drop out all the appearances stuff as it just makes it hard to read the actual details related to the code submitted:

I think you are looking for this:

proc tabulate data=mydata ;
   class var1 var2 var3 / order=data  missing;
   var  Num_of_cases total_cases percentage /;
   keyword all sum  /;

  table  var1=' ' *      (num_of_cases='Number of cases' total_cases='sum of all cases' percentage='Percentages'*f=percent8.2) 
         All='Totals per line'*num_of_cases='Number of cases' , 
        (sum=' '* var2=''*var3=' ')  
       / row=float nocontinued indent=1 rts=2  misstext=[label=" " ]    
        box=[label=' '] ;
run;

When you use () to group things like (var1 all) *( thisvar thatvar anothervar) you forces both VAR1 and ALL to use the variables in the second (). Separate out the All and just use * with the desired variable(s).

[Anita_n]

@ballardw Thankyou for that, that is what I wanted.

Just one more question, if I want to do row sums without the percentage rows. How does this work.

I tried but am not getting it. The percentage row are directly below the absolute values(rows) 

[ballardw]

---

@Anita_n wrote:

@ballardw Thankyou for that, that is what I wanted.

Just one more question, if I want to do row sums without the percentage rows. How does this work.

I tried but am not getting it. The percentage row are directly below the absolute values(rows) 

---

I would have to see a bit of a worked example of exactly what you expect. Any ALL in the Column dimension is going to cross with everything in the rows. If the information is more important than having everything in a single table I would suggest a second Table statement (tabulate can have multiple tables created in one pass with the same Class and Var variables). Just leave out the bits you don't want in another table.

If the intent is to have a single table of a very specific structure you may need to consider either different data structure or perhaps look at a different procedure such as Proc Report (though report doesn't like multiple statistics in a single column) or both more data manipulation/summarizing before creating the report document and different report code.

One of the concerns I had when I read your code was the presence of a variable named Percentage in the first place. Summing or most statistics on a pre-calculated percentage value are either misleading, subject to misunderstanding or incorrect.

Can you provide a small example data set and what you are attempting to get with that data?

[Anita_n]

@ballardw: This is what I actually want : on the right handside I wish to calculate the sum totals per make only for cylinders and not for horsepower and mrsp 

proc tabulate data=sashelp.cars ;
   class make type origin  / order=data  missing;
   var  cylinders horsepower MSRP /;
   keyword all sum  /;

  table  make=' ' *      (cylinders='cylinders' horsepower='horsepower' msrp='msrp')  
         cylinders='cylinders'*all='Totals per country' , 
        (sum=' '* type=''*origin=' ') sum=''*all='Totals cylinders per make' 
       / row=float nocontinued indent=1 rts=2  misstext=[label=" " ]    
        box=[label=' '] ;
run;

[ballardw]

With tabulate doing the calculations the only way not to have the other variables "summed" is to exclude them from the table:

table  make=' ' * (cylinders='cylinders' )  
         cylinders='cylinders'*all='Totals per country' , 
        (sum=' '* type=''*origin=' ') sum=''*all='Totals cylinders per make' 
       / row=float nocontinued indent=1 rts=2  misstext=[label=" " ]    
        box=[label=' '] ;