DATA Step, Macro, Functions and more

Totals in Proc Tabulate

Reply
Frequent Contributor
Posts: 131

Totals in Proc Tabulate

data cars(keep= make type msrp cnt);

set sashelp.cars;

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

cnt=1;

run;

output

Make Type MSRP cnt
Acura SUV $36,945 1
Acura Sedan $23,820 1
Acura Sedan $26,990 1
Acura Sedan $33,195 1
Acura Sedan $43,755 1
Acura Sedan $46,100 1
Acura Sports $89,765 1
BMW SUV $37,000 1
BMW SUV $52,195 1
BMW Sedan $28,495 1
BMW Sedan $30,795 1
BMW Sedan $37,995 1
BMW Sedan $30,245 1
BMW Sedan $35,495 1
BMW Sedan $36,995 1
BMW Sedan $37,245 1
BMW Sedan $39,995 1
BMW Sedan $44,295 1
BMW Sedan $44,995 1
BMW Sedan $54,995 1
BMW Sedan $69,195 1
BMW Sedan $73,195 1
BMW Sports $48,195 1
BMW Sports $56,595 1
BMW Sports $33,895 1
BMW Sports $41,045 1
BMW Wagon $32,845 1

 

present proc tabulate

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

Class make type;

Var msrp cnt;

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

type*(cnt=''*sum='Count of Units' cnt=''*colpctn='% of Units'*f=number8.2

msrp=''*sum='MSRP$' *f=dollar16.2 msrp='' *colpctSum='% of MSRP'*f=number8.2 )

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

*(cnt =' '*sum='Total Exceptions Units' msrp =' '*sum='Total MSRP Dollars$' *f=dollar16.2

cnt=''*colpctn='Total % in Units' *f=number8.2 msrp=''*colpctSum='Total % in Dollars' *f=number8.2)

/ box='Car Summary';

label colpctn = '% of Units';

keylabel n='';

run;

output

Car Summary Type Grand Total
SUV Sedan Sports Wagon
Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Total Exceptions Units Total MSRP Dollars$ Total % in Units Total % in Dollars
Acura 1 33.33 $36,945.00 29.29 5 27.78 $173,860.00 23.56 1 20 $89,765.00 33.31 . . . . 7 $300,570.00 25.93 25.77
BMW 2 66.67 $89,195.00 70.71 13 72.22 $563,935.00 76.44 4 80 $179,730.00 66.69 1 100 $32,845.00 100 20 $865,705.00 74.07 74.23
Total 3 100 $126,140.00 100 18 100 $737,795.00 100 5 100 $269,495.00 100 1 100 $32,845.00 100 27 $1,166,275.00 100 100

Totals show at the bottom.  Is there a way to arrange the proc tabulate code to display the totals at the top instead

  SUV Sedan Sports Wagon        
Car Summary   Grand Total
Total Units 3           Total Dollars $126,1400                      
Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Total Exceptions Units Total MSRP Dollars$ Total % in Units Total % in Dollars
Acura 1 33.33 $36,945.00 29.29 5 27.78 $173,860.00 23.56 1 20 $89,765.00 33.31 . . . . 7 $300,570.00 25.93 25.77
BMW 2 66.67 $89,195.00 70.71 13 72.22 $563,935.00 76.44 4 80 $179,730.00 66.69 1 100 $32,845.00 100 20 $865,705.00 74.07 74.23

And the same across the page

Super User
Posts: 17,868

Re: Totals in Proc Tabulate

It was really hard to find your question buried in there between the tables. 

 

Move around the ALL word in your table statement to control the location. You can play around with your table statement to see where you want it to go. 

 

Ie 

 

TABLE All Make, ...;

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 157 views
  • 0 likes
  • 2 in conversation