Proc tabulate subtotal help

[Q1983]

data cars2(keep= make type origin msrp cnt);

set sashelp.cars;

if make in ('Acura','BMW');

cnt=1;

run;

Proc tabulate data=cars2 order= data format=10. S=[cellwidth=80];

Class make type origin;

Var msrp cnt;

Table make=' ' all={label='Grand Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],

 type=' ' all={label='Grand Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],

 type*(cnt=''*sum='Type Total' *f=number8.)

 origin*(cnt=''*sum='Origin Total' *f=number8.)

 all={ label='Row Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]

 *(cnt =' '*sum='Row Total Units')

 / box='Car Summary';

run;

I want to do the following:

Display Type under the Make (in this  Acura and BMW) with subtotals (row totals and a grand total by row)
Display origin in columnar totals across the report

[Shmuel]

I'm not sure I understand what you want. 

try next code. If it doesn't answer your request please draw a table as you want.

Proc tabulate data=cars2 order= data format=10. S=[cellwidth=80];
Class make type origin;
Var msrp cnt;

Table 
   make=' ' all={label='Grand Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],
  /* type=' ' all={label='Grand Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],*/
   type*(cnt=''*sum='Type Total' *f=number8.)
   origin*(cnt=''*sum='Origin Total' *f=number8.)
   all={ label='Row Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]
        *(cnt =' '*sum='Row Total Units')
   / box='Car Summary';
run;

[Q1983]

.	SUV	Sedan	Sports	Wagon	Row Total
Acura
Asia	1	6	0	0	7
Europe	0	0	0	0	0
Total Acura	1	6	0	0	7
BMW
Asia	0	0	0	0	0
Europe	2	13	4	1	20
Total BMW	2	13	4	1	20
Grand Total	3	19	4	1	27

This is what I am trying to accomplish.  If it cannot be done in proc tab, I may try proc report

[Shmuel · Accepted solution]

Here is the code you want.

I leave to you to deal with the style.

Proc tabulate data=cars2 order= data format=10. S=[cellwidth=80];
Class make type origin;
Var msrp cnt;

table (make (origin all='Subtotal')) all='Grand Total', 
      (type all='Row Toal')  
   / box='Car Summary'; 
run;

Using proc tabulate:

1) Two dimensions: Table <left column categories> , <row categories> / BOX ...

2) Three dimensions: Table <page categories> , <left column categories> , <row categories> / BOX ...

[Q1983]

thanks for the code. 

1. How would I do a subtotal (row) after the Make and the origin variables. I already have the row total

[Shmuel]

In your case the row is made of TYPE only, so that subtotal not needed.

Check next code and compare to the previous:

Proc tabulate data=cars2 order= data format=10. S=[cellwidth=80];
Class make type origin;
Var msrp cnt;

table 
    (type all='Column Toal'),
    (make (origin all='Subtotal')) all='Row Total'       
   / box='Car Summary'; 
run;