TABULATE Procedure

Example 12: Calculating Various Percentage Statistics

Features:

PROC TABULATE statement options: FORMAT=

TABLE statement:: ALL class variable; COLPCTSUM statistic; concatenation (blank) operator; crossing (*) operator; format modifiers; grouping elements (parentheses) operator; labels; REPPCTSUM statistic; ROWPCTSUM statistic; variable list

TABLE statement options:: ROW=FLOAT; RTS=

Other features:

FORMAT procedure

Details

This example shows how to use three percentage sum statistics: COLPCTSUM, REPPCTSUM, and ROWPCTSUM.

Program

data fundrais;
   length name $ 8 classrm $ 1;
   input @1 team $ @8 classrm $ @10 name $
         @19 pencils @23 tablets;
   sales=pencils + tablets;
   datalines;
BLUE   A ANN       4   8
RED    A MARY      5  10
GREEN  A JOHN      6   4
RED    A BOB       2   3
BLUE   B FRED      6   8
GREEN  B LOUISE   12   2
BLUE   B ANNETTE   .   9
RED    B HENRY     8  10
GREEN  A ANDREW    3   5
RED    A SAMUEL   12  10
BLUE   A LINDA     7  12
GREEN  A SARA      4   .
BLUE   B MARTIN    9  13
RED    B MATTHEW   7   6
GREEN  B BETH     15  10
RED    B LAURA     4   3
;

proc format;
   picture pctfmt low-high='009 %';
run;

title "Fundraiser Sales";

proc tabulate format=7.;

   class team classrm;

   var sales;

   table (team all),

         classrm='Classroom'*sales=' '*(sum
         colpctsum*f=pctfmt9.
         rowpctsum*f=pctfmt9.
         reppctsum*f=pctfmt9.)
         all*sales*sum=' '

         /rts=20;
run;

Program Description

Create the FUNDRAIS data set.FUNDRAIS contains data on student sales during a school fund-raiser. A DATA step creates the data set.

data fundrais;
   length name $ 8 classrm $ 1;
   input @1 team $ @8 classrm $ @10 name $
         @19 pencils @23 tablets;
   sales=pencils + tablets;
   datalines;
BLUE   A ANN       4   8
RED    A MARY      5  10
GREEN  A JOHN      6   4
RED    A BOB       2   3
BLUE   B FRED      6   8
GREEN  B LOUISE   12   2
BLUE   B ANNETTE   .   9
RED    B HENRY     8  10
GREEN  A ANDREW    3   5
RED    A SAMUEL   12  10
BLUE   A LINDA     7  12
GREEN  A SARA      4   .
BLUE   B MARTIN    9  13
RED    B MATTHEW   7   6
GREEN  B BETH     15  10
RED    B LAURA     4   3
;

Create the PCTFMT. format.The FORMAT procedure creates a format for percentages. The PCTFMT. format writes all values with at least one digit, a blank, and a percent sign.

proc format;
   picture pctfmt low-high='009 %';
run;

Specify the title.

title "Fundraiser Sales";

Create the report and specify the table options.The FORMAT= option specifies up to seven digits as the default format for the value in each table cell.

proc tabulate format=7.;

Specify subgroups for the analysis.The CLASS statement identifies Team and Classrm as class variables.

   class team classrm;

Specify the analysis variable.The VAR statement specifies that PROC TABULATE calculate statistics on the Sales variable.

   var sales;

Define the table rows.The row dimension of the TABLE statement creates a row for each formatted value of Team. The last row of the report summarizes sales for all teams.

   table (team all),

Define the table columns.The column dimension of the TABLE statement creates a column for each formatted value of Classrm. Crossed within each value of Classrm is the analysis variable (sales) with a blank label. Nested within each column are columns that summarize sales for the class. The first nested column, labeled sum, is the sum of sales for the row for the classroom. The second nested column, labeled ColPctSum, is the percentage of the sum of sales for the row for the classroom in relation to the sum of sales for all teams in the classroom. The third nested column, labeled RowPctSum, is the percentage of the sum of sales for the row for the classroom in relation to the sum of sales for the row for all classrooms. The fourth nested column, labeled RepPctSum, is the percentage of the sum of sales for the row for the classroom in relation to the sum of sales for all teams for all classrooms. The last column of the report summarizes sales for the row for all classrooms.

         classrm='Classroom'*sales=' '*(sum
         colpctsum*f=pctfmt9.
         rowpctsum*f=pctfmt9.
         reppctsum*f=pctfmt9.)
         all*sales*sum=' '

Specify the row title space and eliminate blank row headings.RTS= provides 20 characters per line for row headings.

         /rts=20;
run;

Output

Details

Here are the percentage sum statistic calculations used to produce the output for the Blue Team in Classroom A:

COLPCTSUM=31/91*100=34%
ROWPCTSUM=31/67*100=46%
REPPCTSUM=31/204*100=15%

Similar calculations were used to produce the output for the remaining teams and classrooms.