8.3 Frequency & Categorical Analysis with PROC FREQ

8.3 Frequency & Categorical Analysis with PROC FREQ

While PROC MEANS focuses on continuous numeric summary statistics, PROC FREQ is the core SAS Base procedure for analyzing discrete categorical data. PROC FREQ computes one-way frequency tables, two-way crosstabulations, and multi-way n-way tables. It is heavily utilized for data validation, quality auditing, discovering distribution counts, and calculating statistical measures of association. Mastering PROC FREQ statement options, cell layout controls, missing value rules, and output dataset generation is vital for the SAS Certified Specialist Base Programming exam.

---

1. One-Way Frequency Tables

A one-way frequency table calculates frequency counts, overall percentages, cumulative frequencies, and cumulative percentages for individual categorical variables.

proc freq data=sashelp.shoes;
   tables Region Product;
run;

Syntax Distinction: Placing multiple variables in a single TABLES statement separated by spaces (tables A B;) requests separate one-way tables for variable A and variable B independently.

Default One-Way Table Statistics:

1. Frequency: The count of non-missing observations matching that specific formatted category.
2. Percent: The percentage of total non-missing observations represented by that category (Frequency / Total Non-Missing * 100).
3. Cumulative Frequency: The running cumulative sum of frequencies up to and including the current table row.
4. Cumulative Percent: The running cumulative percentage up to and including the current table row.

/* Suppressing Cumulative Statistics using NOCUM */
proc freq data=sashelp.shoes;
   tables Region / nocum;
run;

---

2. Categorical Grouping via User-Defined Formats

PROC FREQ aggregates data based on the formatted values of variables rather than their raw unformatted values. This allows you to apply user-defined formats created with PROC FORMAT to group continuous or detailed categorical data into custom reporting bins without creating new variables in a DATA step.

proc format;
   value agegrp
      low -< 18 = 'Under 18'
      18  -< 65 = '18 to 64'
      65  - high= '65 and Over';
run;

proc freq data=work.patients;
   tables Age;
   format Age agegrp.;
run;

---

3. Two-Way and Multi-Way Crosstabulation Tables

To examine relationships between two or more categorical variables, join variable names with an asterisk (*) in the TABLES statement.

/* Two-way Crosstabulation: Region (rows) by Product (columns) */
proc freq data=sashelp.shoes;
   tables Region * Product;
run;

Anatomy of a Two-Way Crosstab Cell

By default, each cell in a two-way crosstabulation matrix displays four statistics:

Options for Suppressing Cell Statistics

In exam questions, candidates are frequently asked how to customize crosstab cell contents by suppressing unwanted statistical components using TABLES statement options:

proc freq data=sashelp.shoes;
   tables Region * Product / nopercent norow nocol;
run;

• NOPERCENT: Suppresses display of overall cell percentages.
• NOROW: Suppresses display of row percentages.
• NOCOL: Suppresses display of column percentages.
• NOCUM: Suppresses cumulative statistics in one-way tables.

---

4. Multi-Way Tables & Display Layout Options (LIST & CROSSLIST)

When three or more variables are crossed (e.g., tables A * B * C;), PROC FREQ produces a separate two-way table for each level of the first variable (A).

To alter the visual layout of multi-way tables, use layout options:

• LIST: Displays multi-way combinations in a single linear list format (resembling a one-way table) rather than multiple nested grid matrices.
• CROSSLIST: Displays multi-way tables in an extended ODS cross-tabular list structure with headers.

proc freq data=sashelp.shoes;
   tables Region * Subsidiary * Product / list;
run;

---

5. The NLEVELS Summary Option

The NLEVELS option is specified directly on the PROC FREQ statement line (NOT on the TABLES statement line). It generates a concise summary table at the beginning of the output displaying the number of distinct levels (unique values) and missing levels for each variable listed in the TABLES statement.

proc freq data=sashelp.shoes nlevels;
   tables Region Product Subsidiary;
run;

---

6. Controlling Row Order with ORDER=

The content guide pairs NLEVELS with ORDER=, and ORDER= is the option candidates most often forget. It is specified on the PROC FREQ statement and decides the sequence in which category rows appear.

/* Rank product categories from most common to least common */
proc freq data=sashelp.shoes order=freq;
   tables Product / nocum;
run;

Exam Tip: ORDER= belongs on the PROC FREQ statement line, exactly like NLEVELS. Options such as NOCUM, NOPERCENT, MISSING, LIST, and OUT= belong after the slash on the TABLES statement. Mixing the two up is a classic distractor.

---

7. Using PROC FREQ to Validate Data

The content guide lists "Use PROC FREQ to validate data in a SAS data set" as its own objective. PROC FREQ is a data-quality instrument, not just a reporting one, because a one-way table over a supposedly clean variable exposes four defect classes at a glance:

1. Invalid categories — a Sex column that reports M, F, f, and Male has a standardization problem.
2. Unexpected cardinality — NLEVELS shows 4,812 distinct values in a column meant to hold 50 state codes.
3. Missing data volume — the Frequency Missing line at the foot of the table quantifies gaps without writing a DATA step.
4. Impossible combinations — a two-way table of Country * State that produces cells for Canada * TX.

/* Validation pass over a freshly imported file */
proc freq data=work.imported_customers nlevels;
   tables Sex State Status / missing nocum;
run;

Because PROC FREQ tabulates formatted values, a validation format turns the same step into an explicit range check: every value that falls outside the expected ranges collapses into a single 'INVALID' row that is trivial to spot.

proc format;
   value agecheck
      0 -< 18   = 'Under 18 (check)'
      18 - 120  = 'Valid adult age'
      other     = 'INVALID or missing';
run;

proc freq data=work.patients;
   tables Age / missing;
   format Age agecheck.;
run;

---

8. Handling Missing Values: Default vs. MISSING vs. MISSPRINT

By default, PROC FREQ excludes missing values from frequency count percentages and summary statistics calculations. Missing values are displayed at the bottom of the output table as a separate note (e.g., Frequency Missing = 12).

proc freq data=work.customer_survey;
   tables Income_Group / missing;
run;

---

9. Analyzing Pre-Aggregated Data with the WEIGHT Statement

If your input dataset already contains pre-summarized cell counts rather than individual raw observation rows, use the WEIGHT statement to specify the variable containing observation counts:

data work.grouped_counts;
   input Gender $ Status $ Count;
   datalines;
Male Pass 45
Male Fail 5
Female Pass 48
Female Fail 2
;
run;

proc freq data=work.grouped_counts;
   tables Gender * Status;
   weight Count; /* Treats each row as representing 'Count' observations */
run;

---

10. Exporting Results with OUT= & The SPARSE Option

The OUT= option on the TABLES statement exports frequency counts and percentages to a SAS dataset.

proc freq data=sashelp.shoes;
   tables Region * Product / out=work.freq_counts sparse noprint;
run;

• OUT=dataset: Creates an output dataset containing columns for table variables, COUNT (cell count), and PERCENT (overall percent).
• SPARSE: Forces PROC FREQ to output zero-count combinations (COUNT=0) for category pairs that do not exist in the raw input data.
• CHISQ: Computes Chi-Square tests of independence for two-way tables.