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
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 ...
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;
. | 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
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 ...
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
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.