SAS Oneway Frequency Tabulations and Twoway Contingency Tables (Crosstabs)
/***********************************************************
This example illustrates:
How to create user-defined formats
How to recode continuous variables into ordinal categories
How to generate oneway and twoway tables and basic tests
The following tests are illustrated:
Chi-square goodness of fit test
Binomial test of proportion for a two-level variable
Exact Binomial test
Pearson Chi-square test
Fisher’s exact test
Cochran-Armitage test for trend
Procs used:
Proc Format
Proc Means
Proc Freq
Proc Contents
Filename: frequencies.sas
************************************************************/
OPTIONS FORMCHAR="|----|+|---+=|-/\<>*";
OPTIONS NODATE PAGENO=1 FORMDLIM=" ";
PROC FORMAT;
VALUE AGEFMT 1 = "1:19-29"
2 = "2:30-39"
3 = "3:>39";
VALUE HIAGEFMT 1 = "1:AGE > 39"
2 = "2:AGE <= 39";
VALUE HIHICHOLFMT 1 = "1:>=240"
2 = "2:<240";
VALUE CHOLCATFMT 1 = "1:<200"
2 = "2:200-239"
3 = "3:>=240";
VALUE PILLFMT 1 = "1:PILL"
2 = "2:NO PILL";
VALUE WTFMT 1 = "1:<120"
2 = "2:120-139"
3 = "3:>=140";
VALUE HIBMIFMT 1 = "1:BMI>23"
2 = "2:BMI<=23";
RUN;
The log from that results from these Proc Format commands is shown below. These formats will be stored in the Work library, and thus will be temporary. In the document that follows, you will see the formats being applied within each procedure, by using a format statement. These formats will not be automatically attached to variables, and have to be specified for each procedure.
4 PROC FORMAT;
5 VALUE AGEFMT 1 = "1: Age 19-29"
6 2 = "2: Age 30-39"
7 3 = "3: Age >39";
NOTE: Format AGEFMT has been output.
8
9 VALUE HIAGEFMT 1 = "1: Age >39"
10 2 = "2: Age <=39";
NOTE: Format HIAGEFMT has been output.
11
12 VALUE HICHOLFMT 1 = "1: Chol >=240"
13 2 = "2: Chol <240";
NOTE: Format HICHOLFMT has been output.
14
15 VALUE CHOLCATFMT 1 = "1: Chol <200"
16 2 = "2: Chol 200-239"
17 3 = "3: Chol >=240";
NOTE: Format CHOLCATFMT has been output.
18
19 VALUE PILLFMT 1 = "1: Pill"
20 2 = "2: No Pill";
NOTE: Format PILLFMT has been output.
21
22 VALUE WTFMT 1 = "1: Wt <120kg"
23 2 = "2: Wt 120-139kg"
24 3 = "3: Wt >=140kg";
NOTE: Format WTFMT has been output.
25
26 VALUE HIBMIFMT 1 = "1: BMI>23"
27 2 = "2: BMI<=23";
NOTE: Format HIBMIFMT has been output.
28 RUN;
NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.25 seconds
cpu time 0.00 seconds
Now, we create a permanent SAS data set from the raw data file, Werner2.dat. The raw data are read in, then missing value codes are assigned appropriately, and new variables are created. Note that missing values are assigned before the new variables are created.
libname b510 "e:\510\";
DATA B510.WERNER;
INFILE "werner2.dat";
INPUT ID 1-4 AGE 5-8 HT 9-12 WT 13-16
PILL 17-20 CHOL 21-24 ALB 25-28
CALC 29-32 URIC 33-36 PAIR 37-39;
IF HT = 999 then HT = .;
IF WT = 999 then WT = .;
IF ALB = 99 then ALB = .;
IF CALC = 99 then CALC = .;
IF URIC = 99 then URIC = .;
WTKG = WT*.39;
HTCM = HT*2.54;
BMI = WTKG/(HTCM/100)**2;
IF BMI > 23 then HIBMI = 1;
IF 0<=BMI<=23 then HIBMI = 2;
IF AGE NOT=. THEN DO;
IF AGE <= 29 THEN AGEGROUP=1;
IF AGE > 29 AND AGE <= 39 THEN AGEGROUP=2;
IF AGE > 39 THEN AGEGROUP=3;
IF AGE > 39 THEN HIAGE=1;
IF AGE <= 39 THEN HIAGE=2;
END;
IF CHOL >= 400 OR CHOL < 100 THEN CHOL=.;
IF CHOL NOT=. THEN DO;
IF CHOL >= 240 THEN HICHOL=1;
IF CHOL < 240 THEN HICHOL=2;
IF CHOL < 200 THEN CHOLCAT=1;
IF CHOL >= 200 AND CHOL < 240 THEN CHOLCAT=2;
IF CHOL >= 240 THEN CHOLCAT=3;
END;
IF WT NOT=. THEN DO;
IF WT < 120 THEN WTCAT=1;
IF WT >= 120 AND WT < 140 THEN WTCAT=2;
IF WT >= 140 THEN WTCAT=3;
END;
DROP WTKG HTCM;
RUN;
We use two methods for checking the newly created variables. The simplest one is Proc Means. This tells us most importantly if we have included all cases in our new variables, and if we have avoided adding data where there should be none! We will carefully examine the sample size for each original variable, and each new variable that was created, to be sure they match. This simple check should always be done first!
TITLE "DESCRIPTIVE STATISTICS";
PROC MEANS;RUN;
DESCRIPTIVE STATISTICS
The MEANS Procedure
Variable N Mean Std Dev Minimum Maximum
-------------------------------------------------------------------------------
ID 188 1598.96 1057.09 3.0000000 3519.00
AGE 188 33.8191489 10.1126942 19.0000000 55.0000000
HT 186 64.5107527 2.4850673 57.0000000 71.0000000
WT 186 131.6720430 20.6605767 94.0000000 215.0000000
PILL 188 1.5000000 0.5013351 1.0000000 2.0000000
CHOL 186 236.1505376 42.5555145 155.0000000 390.0000000
ALB 186 4.1112903 0.3579694 3.2000000 5.0000000
CALC 185 9.9621622 0.4795556 8.6000000 11.1000000
URIC 187 4.7705882 1.1572312 2.2000000 9.9000000
PAIR 188 47.5000000 27.2063810 1.0000000 94.0000000
BMI 184 19.0736235 2.6285786 15.2305671 29.6996059
HIBMI 184 1.9021739 0.2978899 1.0000000 2.0000000
AGEGROUP 188 1.9255319 0.8432096 1.0000000 3.0000000
HIAGE 188 1.6808511 0.4673916 1.0000000 2.0000000
HICHOL 186 1.5322581 0.5003051 1.0000000 2.0000000
CHOLCAT 186 2.2634409 0.7783954 1.0000000 3.0000000
WTCAT 186 2.0322581 0.7490767 1.0000000 3.0000000
-------------------------------------------------------------------------------
A second way to check recodes of continuous variables into categories is illustrated below. Basically, you can check the minimum and maximum value of the original variable in each category of the new categorical variable to be sure the range of values is specified as you wanted it to be. Do this only after you have checked the sample sizes by using a simple Proc Means statement, as illustrated above.
TITLE "CHECKING RECODE OF WT INTO WTCAT";
PROC MEANS DATA=B510.WERNER;
CLASS WTCAT;
VAR WT;
FORMAT WTCAT WTFMT.;
RUN;
CHECKING RECODE OF WT INTO WTCAT
The MEANS Procedure
Analysis Variable : WT
N
WTCAT Obs N Mean Std Dev Minimum Maximum
---------------------------------------------------------------------------------------------
1: Wt <120kg 49 49 109.4489796 7.0209841 94.0000000 119.0000000
2: Wt 120-139kg 82 82 128.6097561 5.9103510 120.0000000 138.0000000
3: Wt >=140kg 55 55 156.0363636 17.2969315 140.0000000 215.0000000
---------------------------------------------------------------------------------------------
TITLE "CHECKING RECODE OF AGE INTO AGEGROUP";
PROC MEANS DATA=B510.WERNER;
CLASS AGEGROUP;
VAR AGE;
FORMAT AGEGROUP AGEFMT.;
RUN;
CHECKING RECODE OF AGE INTO AGEGROUP
The MEANS Procedure
Analysis Variable : AGE
N
AGEGROUP Obs N Mean Std Dev Minimum Maximum
------------------------------------------------------------------------------------------
1: Age 19-29 74 74 23.8378378 2.7846302 19.0000000 29.0000000
2: Age 30-39 54 54 33.5925926 3.0376165 30.0000000 39.0000000
3: Age >39 60 60 46.3333333 4.6892111 40.0000000 55.0000000
------------------------------------------------------------------------------------------
TITLE "CHECKING RECODE OF CHOL INTO HICHOL";
PROC MEANS DATA=B510.WERNER;
CLASS HICHOL;
VAR CHOL;
FORMAT HICHOL HICHOLFMT.;
RUN;
CHECKING RECODE OF CHOL INTO HICHOL
The MEANS Procedure
Analysis Variable : CHOL
N
HICHOL Obs N Mean Std Dev Minimum Maximum
-------------------------------------------------------------------------------------------
1: Chol >=240 87 87 272.4712644 29.0159696 240.0000000 390.0000000
2: Chol <240 99 99 204.2323232 21.8985734 155.0000000 238.0000000
-------------------------------------------------------------------------------------------
TITLE "CHECKING RECODE OF CHOL INTO CHOLCAT";
PROC MEANS DATA=B510.WERNER;
CLASS CHOLCAT;
VAR CHOL;
FORMAT CHOLCAT CHOLCATFMT.;
RUN;
CHECKING RECODE OF CHOL INTO CHOLCAT
The MEANS Procedure
Analysis Variable : CHOL
N
CHOLCAT Obs N Mean Std Dev Minimum Maximum
---------------------------------------------------------------------------------------------
1: Chol <200 38 38 181.7631579 12.9894639 155.0000000 198.0000000
2: Chol 200-239 61 61 218.2295082 12.6601651 200.0000000 238.0000000
3: Chol >=240 87 87 272.4712644 29.0159696 240.0000000 390.0000000
---------------------------------------------------------------------------------------------
TITLE "ONEWAY FREQUENCIES";
PROC FREQ DATA=B510.WERNER ORDER=INTERNAL;
TABLES PILL WTCAT AGEGROUP HIAGE HICHOL;
FORMAT AGEGROUP AGEFMT. HICHOL HICHOLFMT. PILL PILLFMT. WTCAT WTFMT. HIAGE HIAGEFMT.;
RUN;
ONEWAY FREQUENCIES
The FREQ Procedure
Cumulative Cumulative
PILL Frequency Percent Frequency Percent
---------------------------------------------------------------
1: Pill 94 50.00 94 50.00
2: No Pill 94 50.00 188 100.00
Cumulative Cumulative
WTCAT Frequency Percent Frequency Percent
--------------------------------------------------------------------
1: Wt <120kg 49 26.34 49 26.34
2: Wt 120-139kg 82 44.09 131 70.43
3: Wt >=140kg 55 29.57 186 100.00
Frequency Missing = 2
Cumulative Cumulative
AGEGROUP Frequency Percent Frequency Percent
-----------------------------------------------------------------
1: Age 19-29 74 39.36 74 39.36
2: Age 30-39 54 28.72 128 68.09
3: Age >39 60 31.91 188 100.00
Cumulative Cumulative
HIAGE Frequency Percent Frequency Percent
----------------------------------------------------------------
1: Age >39 60 31.91 60 31.91
2: Age <=39 128 68.09 188 100.00
Cumulative Cumulative
HICHOL Frequency Percent Frequency Percent
------------------------------------------------------------------
1: Chol >=240 87 46.77 87 46.77
2: Chol <240 99 53.23 186 100.00
Frequency Missing = 2
Cumulative Cumulative
CHOLCAT Frequency Percent Frequency Percent
--------------------------------------------------------------------
1: Chol <200 38 20.43 38 20.43
2: Chol 200-239 61 32.80 99 53.23
3: Chol >=240 87 46.77 186 100.00
Frequency Missing = 2
One-Sample Tests for Categorical Variables
Binomial Confidence Intervals and Tests for Binary Variables:
If you have a categorical variable with only two levels, you can use the binomial option to request a 95% confidence interval for the proportion in the first level of the variable, and a test of the null hypothesis:
H0: proportion in first category of the variable = π
In the option (P= ) you specify the hypothesized proportion in the first category of the tabled variable. By default, SAS reports both one-sided and two-sided asymptotic p-values.
TITLE "BINOMIAL TEST";
PROC FREQ DATA=B510.WERNER ORDER=INTERNAL;
TABLES HIBMI / BINOMIAL (P=.20);
FORMAT HIBMI HIBMIFMT.;
RUN;
The hypotheses that we are testing are shown below:
H0: proportion with high bmi = 0.20
HA: proportion with high bmi ¹ 0.20
BINOMIAL TEST
Cumulative Cumulative
HIBMI Frequency Percent Frequency Percent
--------------------------------------------------------------
1:BMI>23 18 9.78 18 9.78
2:BMI<=23 166 90.22 184 100.00
Frequency Missing = 4
Binomial Proportion
for HIBMI = 1:BMI>23
-------------------------------------
Proportion (P) 0.0978
ASE 0.0219
95% Lower Conf Limit 0.0549
95% Upper Conf Limit 0.1408
Exact Conf Limits
95% Lower Conf Limit 0.0590
95% Upper Conf Limit 0.1502
Test of H0: Proportion = 0.2
ASE under H0 0.0295
Z -3.4649
One-sided Pr < Z 0.0003
Two-sided Pr > |Z| 0.0005
If you wish to obtain an exact binomial test of the null hypothesis, use the exact statement.
PROC FREQ DATA=B510.WERNER ORDER=INTERNAL;
TABLES HIBMI / BINOMIAL (P=.20);
exact binomial;
FORMAT HIBMI HIBMIFMT.;
RUN;
This results in an exact test of the null hypothesis, in addition to the default asymptotic test.
Exact Test
One-sided Pr <= P 1.415E-04
Two-sided = 2 * One-sided 2.829E-04
Chi-square Goodness of Fit Tests for Categorical Variables:
Use the chisq option in the tables statement to get a chi-square goodness of fit test, which can be used for categorical variables with two or more levels. By default SAS assumes that you wish to test the null hypothesis that the proportion of cases is equal in all categories.
Use the testp= option to specify the proportions that you wish to test, if you don't want to assume equal proportions in all categories. The total of all the proportions must be 1.0. You can also use percentages, in which case, the total must add up to 100%. Give the appropriate proportions in the testp= option, specifying them in order as they apply to each category.
TITLE "CHISQUARE GOODNESS OF FIT TEST";
PROC FREQ DATA=B510.WERNER ORDER=INTERNAL;
TABLES CHOLCAT / CHISQ TESTP=(.20 .30 .50);
FORMAT CHOLCAT CHOLCATFMT.;
RUN;
The null hypothesis that we are testing is:
H0: π1= 0.20, π2 = .30, π3 = .50
CHISQUARE GOODNESS OF FIT TEST
The FREQ Procedure
Test Cumulative Cumulative
CHOLCAT Frequency Percent Percent Frequency Percent
--------------------------------------------------------------------------------
1: Chol <200 38 20.43 20.00 38 20.43
2: Chol 200-239 61 32.80 30.00 99 53.23
3: Chol >=240 87 46.77 50.00 186 100.00
Frequency Missing = 2
Chi-Square Test
for Specified Proportions
-------------------------
Chi-Square 0.8889
DF 2
Pr > ChiSq 0.6412
Effective Sample Size = 186
Frequency Missing = 2
Two-Sample Tests for Categorical Variables:
Chi-Square test of Independence
Two by Two Table:
If you wish to examine the relationship between two categorical variables, you can use Proc Freq. Use the chisq option to obtain the Pearson chi-square test of independence (or of homogeneity), and use the expected option to get the expected value in each cell. The commands below can be used to get a cross-tabulation. In this case, we have a 2 by 2 table, because each categorical variable has two levels. We test:
H0: HIAGE is independent of HICHOL status
HA: HIAGE is not independent of HICHOL status
Note that Fisher’s exact test is produced by default for a 2 x 2 table, when the chisq option is specified. Read either the one-sided or two-sided p-value for the Fisher’s exact test, which are at the bottom of the respective panel of output, and shown in bold below.
TITLE "2x2 TABLE";
PROC FREQ DATA=B510.WERNER ORDER=INTERNAL;
TABLES HIAGE*HICHOL / CHISQ EXPECTED;
FORMAT HIAGE HIAGEFMT. HICHOL HICHOLFMT.;RUN;
2x2 TABLE
Table of HIAGE by HICHOL
HIAGE HICHOL
Frequency |
Expected |
Percent |
Row Pct |
Col Pct |1: Chol |2: Chol | Total
|>=240 |<240 |
------------+--------+--------+
1: Age >39 | 42 | 18 | 60
| 28.065 | 31.935 |
| 22.58 | 9.68 | 32.26
| 70.00 | 30.00 |
| 48.28 | 18.18 |
------------+--------+--------+
2: Age <=39 | 45 | 81 | 126
| 58.935 | 67.065 |
| 24.19 | 43.55 | 67.74
| 35.71 | 64.29 |
| 51.72 | 81.82 |
------------+--------+--------+
Total 87 99 186
46.77 53.23 100.00
Frequency Missing = 2
Statistics for Table of HIAGE by HICHOL
Statistic DF Value Prob
------------------------------------------------------
Chi-Square 1 19.1914 <.0001
Likelihood Ratio Chi-Square 1 19.5296 <.0001
Continuity Adj. Chi-Square 1 17.8389 <.0001
Mantel-Haenszel Chi-Square 1 19.0882 <.0001
Phi Coefficient 0.3212
Contingency Coefficient 0.3058
Cramer's V 0.3212
Fisher's Exact Test
----------------------------------
Cell (1,1) Frequency (F) 42
Left-sided Pr <= F 1.0000
Right-sided Pr >= F 1.045E-05
Table Probability (P) 8.118E-06
Two-sided Pr <= P 1.741E-05
Effective Sample Size = 186
Frequency Missing = 2
Cochran-Armitage test for trend:
R x 2 table, or 2 x C table
The Cochran-Armitage test for trend is appropriate when either the row or column variable is binary (has two levels) and the other variable is ordinal. It tests whether there is a linear trend in the proportion of subjects having the binary characteristic. The Mantel-Haenszel test statistic tests for a linear by linear association and can be used when both row and column variables are ordinal; it always has 1 degree of freedom. In the table below, both the row and column variables could be considered to be ordinal, because a binary variable can be thought of as a very simple case of an ordinal variable.