topic Re: Proc tabulate subtotal help in SAS Programming

Proc tabulate subtotal help

Q1983 — Sat, 20 Apr 2019 23:15:10 GMT

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

Re: Proc tabulate subtotal help

Shmuel — Sun, 21 Apr 2019 07:28:11 GMT

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;

Re: Proc tabulate subtotal help

Q1983 — Sun, 21 Apr 2019 18:55:31 GMT

.	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

Re: Proc tabulate subtotal help

Shmuel — Sun, 21 Apr 2019 19:41:29 GMT

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 ...

Re: Proc tabulate subtotal help

Q1983 — Mon, 22 Apr 2019 01:59:40 GMT

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

Re: Proc tabulate subtotal help

Shmuel — Mon, 22 Apr 2019 05:24:20 GMT

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;