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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.