Anyone Can Learn PROC TABULATE

1
Anyone Can Learn PROC TABULATE

• Lauren HaworthGenentech, Inc., South San
  Francisco

2
Introduction

• No matter how you organize and analyze your data,
  chances are youll end up producing a report in
  the form of a table
• This is why every SAS user needs to know how to
  use PROC TABULATE

3
Unfortunately, TABULATE has gotten a bad rap as
being a difficult procedure to learn.

• This paper will prove that if you take things
  step by step, anyone can learn PROC TABULATE.

4
This paper will cover

• basic one-dimensional tables
• two-dimensional tables
• tables with totals
• three-dimensional tables
• cleaning up your tables
• creating HTML output

5
The Simplest Possible Table

• PROC TABULATE DATATEMP

6
The Simplest Possible Table

• PROC TABULATE DATATEMP TABLE RENTRUN

7
The Simplest Possible Table

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTRUN

8
Output

• Rent
• Sum
• 264514.00

9
Adding a Statistic

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTRUN

10
Adding a Statistic

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTMEANRUN

11
Output

• Rent
• Mean
• 1335.93

12
Adding Another Statistic

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTMEANRUN

13
Adding Another Statistic

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTNRUN

14
Adding Another Statistic

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTN RENTMEANRUN

15
Output

• Rent Rent
• N Mean
• 198.00 1335.93

16
Using Parentheses

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTN RENTMEANRUN

17
Using Parentheses

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENT(N MEAN)RUN

18
Output

• Rent
• N Mean
• 198.00 1335.93

19
Adding a Classification Variable

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENTMEANRUN

20
Adding a Classification Variable

• PROC TABULATE DATATEMP CLASS CITY VAR
  RENT TABLE RENTMEANRUN

21
Adding a Classification Variable

• PROC TABULATE DATATEMP CLASS CITY VAR
  RENT TABLE RENTMEANCITYRUN

22
Output

• Rent
• Mean
• City
• San
• Portland Francisco Long Beach
• 931.44 2282.22 1116.00

23
Two-dimensional tables

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENT(N MEAN)RUN

24
Output

• Rent
• N Mean
• 198.00 1335.93

25
Adding the second dimension

• PROC TABULATE DATATEMP VAR RENT TABLE
  RENT(N MEAN)RUN

26
Adding the second dimension

• PROC TABULATE DATATEMP CLASS BEDROOMS
  VAR RENT TABLE RENT(N MEAN)RUN

27
Adding the second dimension

• PROC TABULATE DATATEMP CLASS BEDROOMS
  VAR RENT TABLE BEDROOMS, RENT(N
  MEAN)RUN

28
Output

• Rent
• N Mean
• Bedrooms
• 1 Bedroom 92.00 1180.35
• 2 Bedrooms 106.00 1470.96

29
Adding class variables on both dimensions

• PROC TABULATE DATATEMP CLASS BEDROOMS
  VAR RENT TABLE BEDROOMS, RENTMEANRUN

30
Adding class variables on both dimensions

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS,
  RENTMEANRUN

31
Adding class variables on both dimensions

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS,
  RENTCITYMEANRUN

32
Output

• 
  
• Rent
  
• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  
• 
  
• Mean Mean Mean
  
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92
• 
  

33
Adding Another Classification Variable

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS,
  RENTCITYMEANRUN

34
Adding Another Classification Variable

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT TABLE BEDROOMS,
  RENTCITYMEANRUN

35
Adding Another Classification Variable

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT TABLE BEDROOMS PARKING,
  RENTCITYMEANRUN

36
Output

• 
  
• Rent
  
• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  
• 
  
• Mean Mean Mean
  
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92
• 
  
• Parking
  
• 
  
• No 781.75 1785.30
  1060.54

37
Nesting the Classification Variable

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT TABLE BEDROOMS PARKING,
  RENTCITYMEANRUN

38
Nesting the Classification Variable

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT TABLE BEDROOMSPARKING,
  RENTCITYMEANRUN

39
Output

• 
  
• Rent
  
• 
  
• City
  
• 
  
• San
  
• Portland Francisco
  Long Beach
• 
  
• Mean Mean
  Mean
• 
  
• Bedrooms Parking
  
• 
  
• 1 Bedroom No 707.00
  1586.10 896.00
• 
  
• Yes 843.04
  2405.07 984.85
• 
  
• 2 BedroomsNo 856.50
  1984.50 1151.94
• 
  
• Yes 1127.80
  2795.88 1324.80

40
Adding Totals to the Rows

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, CITYNRUN

41
Output

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  
• 
  
• N N N
  
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 38.00 24.00
  30.00
• 
  
• 2 Bedrooms 42.00 26.00
  38.00
• 
  

42
Adding Totals to the Rows

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, CITYNRUN

43
Adding Totals to the Rows

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, CITYN ALLRUN

44
Adding Totals to the Rows

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, CITYN ALLNRUN

45
Adding Totals to the Rows

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, (CITY ALL)NRUN

46
Output

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• N N N
  N
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 38.00 24.00
  30.00 92.00
• 
  
• 2 Bedrooms 42.00 26.00
  38.00 106.00
• 
  

47
Adding Totals to the Columns

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS, CITYNRUN

48
Adding Totals to the Columns

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  TABLE BEDROOMS ALL, CITYNRUN

49
Output

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  
• 
  
• N N N
  
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 38.00 24.00
  30.00
• 
  
• 2 Bedrooms 42.00 26.00
  38.00
• 
  
• All 80.00 50.00
  68.00
• 
  

50
Three-Dimensional Tables
51
Three-Dimensional Tables

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT
• TABLE BEDROOMS, (CITY ALL)RENTMEANRUN
  

52
Output

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

53
Three-Dimensional Tables

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT
• TABLE BEDROOMS, (CITY ALL)RENTMEANRUN
  

54
Three-Dimensional Tables

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT
• TABLE BEDROOMS, (CITY ALL)RENTMEANRUN
  

55
Three-Dimensional Tables

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  PARKING VAR RENT
• TABLE PARKING, BEDROOMS, (CITY
  ALL)RENTMEANRUN

56
Output

• Parking No
• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 707.00 1586.10
  896.00 1040.78
• 
  
• 2 Bedrooms 856.50 1984.50
  1151.94 1271.45
• 
  

57
Output

• Parking Yes
• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 843.04 2405.07
  984.85 1254.78
• 
  
• 2 Bedrooms 1127.80 2795.88
  1324.80 1591.88
• 
  

58
Making the table pretty
59
Making the table pretty

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

60
Clarifying a Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS, (CITY ALL)
• RENTMEANRUN

61
Clarifying a Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS, (CITY
  ALLOverall)
• RENTMEANRUN

62
Output

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  Overall
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

63
Removing an Extra Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS, (CITY
  ALLOverall)
• RENTMEANRUN

64
Removing an Extra Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS, (CITY
  ALLOverall)
• RENTMEANRUN

65
Output

• 
  
• San
  
• Portland Francisco Long Beach
  Overall
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

66
Removing More Labels

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS, (CITY
  ALLOverall)
• RENTMEANRUN

67
Removing More Labels

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN RUN

68
Output

• 
  
• San
  
• Portland Francisco Long Beach
  Overall
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

69
Adding a Combined Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN RUN

70
Adding a Combined Label

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN
• / BOXAverage RentRUN

71
Output

• 
  
• Average San
  
• Rent Portland Francisco Long Beach
  Overall
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

72
Formatting the Data

• PROC TABULATE DATATEMP CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN
• /BOXAverage RentRUN

73
Formatting the Data

• PROC TABULATE DATATEMP
• FORMATDOLLAR12. CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN
• /BOXAverage RentRUN

74
Output

• 
  
• Average San
  
• Rent Portland Francisco Long Beach
  Overall
• 
  
• 1 Bedroom 800 2,064
  955 1,180
• 
  
• 2 Bedrooms 1,050 2,484
  1,243 1,471
• 
  

75
Before

• 
  
• City
  
• 
  
• San
  
• Portland Francisco Long Beach
  All
• 
  
• Rent Rent Rent
  Rent
• 
  
• Mean Mean Mean
  Mean
• 
  
• Bedrooms
  
• 
  
• 1 Bedroom 800.08 2063.83
  955.23 1180.35
• 
  
• 2 Bedrooms 1050.29 2483.81
  1242.92 1470.96
• 
  

76
After

• 
  
• Average San
  
• Rent Portland Francisco Long Beach
  Overall
• 
  
• 1 Bedroom 800 2,064
  955 1,180
• 
  
• 2 Bedrooms 1,050 2,484
  1,243 1,471
• 
  

77
Creating HTML Output
78
Creating HTML Output V8

• ODS HTML BODYSAMPLE.HTMLPROC TABULATE
  DATATEMP
• FORMATDOLLAR12. CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN
• /BOXAverage RentRUNODS HTML CLOSE

79
Output
80
Changing the Style

• ODS HTML BODYSAMPLE.HTML STYLEBARRETTSBLUE
  PROC TABULATE DATATEMP
• FORMATDOLLAR12. CLASS BEDROOMS CITY
  VAR RENT TABLE BEDROOMS , (CITY
  ALLOverall)
• RENT MEAN
• /BOXAverage RentRUNODS HTML CLOSE

81
Output - BARRETTSBLUE
82
Output - BRICK
83
Output - BROWN
84
Output D3D
85
Output - MINIMAL
86
Output - STATDOC
87
Output - THEME
88
Conclusions

• At this point, you should be able to produce a
  simple table with totals,
• clean it up a bit,
• and create HTML output
• To learn more
• review the TABULATE manual
• read other SUGI papers
• (Warning here comes the commercial)

89
Conclusions

• At this point, you should be able to
• produce a simple table with totals,
• clean it up a bit,
• and create HTML output
• To learn more
• review the TABULATE manual
• read other SUGI papers
• read my book!

90
Thanks for coming!