Filtering, Validating, and Summarizing SAS Datasets

After creating and combining SAS datasets, the next question is simple but important: how do we know that the result is correct, and how do we prepare it for analysis?

In this article we will create a small example dataset, inspect its structure, filter observations, select variables, check for missing values, and produce a few summary statistics.

Creating the example dataset

The following program creates the two source datasets and combines them into people_enriched. The people_registry dataset contains personal information, while people_bmi contains a calculated BMI value for each person.

data people_registry;
    input id name $ surname $ sex $ age weight height;
    datalines;
1 Julia Smith F 29 56 172
2 Mark Ronson M 47 78 182
3 Patrick Lane M 33 69 177
4 Allie White F 45 62 189
5 Owen Peterson M 39 71 176
;
run;

data people_bmi;
    set people_registry;

    bmi = weight / ((height / 100) ** 2);

    keep id bmi;
run;

proc sort data=people_registry;
    by id;
run;

proc sort data=people_bmi;
    by id;
run;

data people_enriched;
    merge people_registry people_bmi;
    by id;
run;

Both datasets are sorted by id because SAS requires the input datasets to be ordered by the BY variable before a MERGE. The resulting people_enriched dataset contains the registry information together with BMI.

Inspecting a dataset

Before transforming a dataset, it is useful to check what it contains. PROC CONTENTS reports metadata such as variable names, types, lengths, and formats:

proc contents data=people_enriched;
run;

To inspect the observations themselves, use PROC PRINT. The OBS= option limits the output to the first five rows:

proc print data=people_enriched(obs=5);
run;

This quick inspection can reveal unexpected variable types, missing columns, or values that were not calculated as expected.

Filtering observations with `WHERE`

Suppose that we want to work only with people aged 40 or older. A WHERE statement keeps the observations that satisfy a condition:

data people_40_plus;
    set people_enriched;
    where age >= 40;
run;

The new people_40_plus dataset contains only the matching observations. WHERE is especially useful when we want to discard observations before the DATA step processes them.

For example, we can keep people with a BMI in the normal range in a second step:

data people_normal_bmi;
    set people_enriched;
    where bmi >= 18.5 and bmi < 25;
run;

Selecting and renaming variables

Filtering observations changes the rows in a dataset. KEEP, DROP, and RENAME change the variables:

data people_analysis;
    set people_enriched;
    keep id name surname age bmi;
    rename bmi = body_mass_index;
run;

The result contains only the variables needed for the analysis, and bmi has a more descriptive name.

The same operations can be written as dataset options:

data people_analysis;
    set people_enriched(keep=id name surname age bmi
                        rename=(bmi=body_mass_index));
run;

Dataset options are useful when the reduced or renamed version is needed only for one step.

`WHERE` versus `IF`

WHERE and IF can both filter observations, but they do not work at exactly the same point in a DATA step.

data adults;
    set people_enriched;
    if age >= 18;
run;

The IF statement runs after SAS has read the observation into the program data vector. This makes it useful when the condition depends on a variable calculated in the same DATA step:

data people_with_bmi_flag;
    set people_registry;

    bmi = weight / ((height / 100) ** 2);

    if bmi >= 25 then bmi_group = "Above normal";
    else bmi_group = "Normal range";
run;

As a practical rule, use WHERE to filter existing variables early and IF when the condition depends on a newly calculated value or more complex DATA step logic.

Checking missing values

Summary procedures make it easy to check whether a dataset contains missing values. PROC MEANS reports the number of non-missing observations (N) and missing observations (NMISS) for numeric variables:

proc means data=people_enriched n nmiss mean min max;
    var age weight height bmi;
run;

For categorical variables, PROC FREQ shows the distribution of values and can also include missing values:

proc freq data=people_enriched;
    tables sex / missing;
run;

This validation step matters after a MERGE: a missing value may indicate that a key did not match in one of the source datasets, rather than simply being an empty field in the original data.

Summarizing numeric variables

PROC MEANS can also provide a compact statistical summary. Here we calculate descriptive statistics for age, weight, height, and BMI:

proc means data=people_enriched n mean std min median max maxdec=2;
    var age weight height bmi;
run;

The output includes the number of observations, mean, standard deviation, minimum, median, and maximum. These values provide a first understanding of the dataset before moving to a more specialized analysis.

We can also summarize a numeric variable by a group. First sort the data by the grouping variable:

proc sort data=people_enriched out=people_by_sex;
    by sex;
run;

proc means data=people_by_sex mean median;
    by sex;
    var bmi;
run;

The BY sex statement produces one BMI summary for each sex category. The data must be sorted by the BY variable before using this form of grouped processing.

Counting categories with `PROC FREQ`

PROC FREQ is designed for categorical variables. It counts the observations in each category and calculates their percentages:

proc freq data=people_enriched;
    tables sex;
run;

For a two-way table, list two variables separated by an asterisk:

proc freq data=people_with_bmi_flag;
    tables sex * bmi_group;
run;

This kind of table is a useful first check when comparing groups or looking for unexpected combinations of values.

A small validation workflow

A practical workflow for the people_enriched dataset is therefore:

proc contents data=people_enriched;
run;

proc print data=people_enriched(obs=5);
run;

proc means data=people_enriched n nmiss mean min median max;
    var age weight height bmi;
run;

proc freq data=people_enriched;
    tables sex / missing;
run;

These four steps answer four basic questions:

What variables and types are present?
Do the first observations look right?
Are numeric values missing or implausible?
How are categorical values distributed?

Conclusion

Filtering and summarizing are the bridge between data preparation and analysis. WHERE, KEEP, DROP, and RENAME help create a focused analysis dataset, while PROC CONTENTS, PROC MEANS, and PROC FREQ help verify that the data is ready to use.

After creating people_enriched with MERGE and BY, these checks should become a normal part of the workflow. They make errors visible before they affect later results.

Frequently asked questions

Why do the datasets need to be sorted before `MERGE`?

SAS uses the BY variable to align observations while it reads the input datasets. Sorting both datasets by that variable ensures that matching keys are processed together and avoids unreliable results.

Should I use `WHERE` or `IF` to filter observations?

Use WHERE when filtering on variables that already exist in the input dataset. Use IF when the condition depends on a variable calculated during the DATA step or on more complex DATA step logic.

What does `NMISS` tell me?

NMISS counts missing values for numeric variables. A non-zero value is a signal to investigate the source data or the preceding transformation before continuing with the analysis.

What should I check if the merged dataset contains missing values?

First check that both input datasets contain the same key variable and that the key values are spelled and typed consistently. Then inspect the sorted inputs and compare their keys to identify records that did not match.

Your next step

Run the complete program in this article from top to bottom, then deliberately change one input value or remove one id from people_bmi. Use PROC CONTENTS, PROC MEANS, and PROC FREQ to see how the validation output changes.

When you are ready to revisit the dataset-combination step, read Combining SAS datasets with MERGE and BY and compare its intermediate datasets with the final people_enriched table. That is the habit this lesson is designed to build: transform the data, check the result, and only then analyse it.

Index

To make it easier to follow this Base SAS guide, here is the current course index:

Introduction to Base SAS
Conditional statements in Base SAS
Combining SAS datasets with MERGE and BY
Filtering, validating, and summarizing SAS datasets

Creating the example dataset

Inspecting a dataset

Filtering observations with WHERE

Selecting and renaming variables

WHERE versus IF