CODEWITHSUNDEEP
sas
user3930756
user3930756

Reputation: 157

How to use PROC TABULATE in SAS to calculate a sub-group's percentage compared to the main group?

Is it possible to use PCTSUM in PROC TABULATE to calculate the what percentage a sub-group (or even a sub-sub-group) takes up compared to the overall group? It's probably best to provide an example.

Here's a sample dataset:

data sample;
input make $ model $ owned rented;
datalines;
Toyota Corolla 400 224
Toyota Camry 750 700
Honda Civic 650 519
Honda Accord 225 203
;

I know the following PROC TABULATE line will give me what percentage of vehicles are rented by make

proc tabulate data=sample;
class make model;
var owned rented;
table (make='Vehicle Make' all), owned='Total Owned'*sum rented='Rented'*(sum='Total Rented' pctsum<owned>='Pct Rented');
run;

Like so:

Veh Make  TotOwned   TotRent    PctRent
Honda     875        722        82.51%
Toyota    1150       924        80.35%
All       2025       1646       81.28%

But is it possible to break that down by model so that it tells us not what percentage of Civics are rented (519/650=79.8%) but what percentage of all Hondas are rented Civics (519/875=59.3%)?

How do I write the PROC TABULATE line so that it shows me this:

Veh Make  VehModel TotOwned   TotRent    PctRent
Honda     Accord   225        203        23.20%
          Civic    650        519        59.31%
          All      875        722        82.51%
Toyota    Camry    750        700        60.87%
          Corolla  400        224        19.48%
          All      1150       924        80.35%
All                2025       1646       81.28%

Note that the 23.2% and 59.31% of the Honda models total up to the 82.51% of the Honda subtotal.

Thanks for any help you can provide.

Upvotes: 0

Views: 1542

Answers (1)

Joe
Joe

Reputation: 63424

The best I can do is to split the table into pages, and use PAGEPCTSUM. You could output this to a dataset and then re-print it (if you are using the printed output) using another PROC TABULATE or a PROC REPORT or similar.

proc tabulate data=sample;
  class make model;
  var owned rented;
  table (make='Vehicle Make'), model='Vehicle Model',
         owned='Total Owned'*sum rented='Rented'*
          (sum='Total Rented' pagepctsum<owned>='Pct Rented');
run;

Upvotes: 1

Related Questions