Combining SAS Datasets with MERGE and BY

TL;DR deck

TL;DR: Combining SAS Datasets with MERGE and BY

A compact visual guide to creating a derived dataset, sorting by a key, and combining SAS tables with MERGE and BY.

After learning how to create a dataset and use conditional statements, a useful next step is learning how to combine datasets. In this article we will use the MERGE statement together with BY to join two small SAS datasets.

Creating the first dataset

We will start with the people_registry dataset used in the previous articles:

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;

This dataset contains the basic information about five people.

Creating a second dataset

Now we create people_bmi. It contains only the id variable and the calculated Body Mass Index, or BMI:

data people_bmi;
    set people_registry;

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

    keep id bmi;
run;

The SET statement reads the observations from people_registry. The KEEP statement limits the output dataset to id and bmi.

At this point, the two datasets look conceptually like this:

`people_registry`	`people_bmi`
`id`, name, surname, sex, age, weight, height	`id`, bmi

The id variable is present in both datasets. We will use it as the key for combining them.

Sorting the datasets

Before using MERGE with a BY statement, sort both datasets by the key variable:

proc sort data=people_registry;
    by id;
run;

proc sort data=people_bmi;
    by id;
run;

The BY id statement tells SAS that observations should be matched according to their id value.

Combining the datasets

We can now create a new dataset containing all the variables from both sources:

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

The resulting people_enriched dataset contains the original registry information together with the calculated BMI:

proc print data=people_enriched;
run;

The important part of the program is:

merge people_registry people_bmi;
by id;

SAS reads the two datasets and places values with the same id on the same observation.

A common error

If SAS reports that WORK.PEOPLE_REGISTRY.DATA does not exist, the dataset has not been created in the current session. Run the DATA step that creates people_registry before running PROC SORT or the MERGE step.

Datasets saved in the WORK library are temporary. They disappear when the SAS session ends, so all the DATA steps need to be run again in a new session unless the datasets are saved in a permanent library.

Conclusion

MERGE and BY are useful when information is split across several datasets. In this example, people_registry held the personal information and people_bmi held a calculated variable. By matching the two datasets with id, we created one enriched dataset ready for further analysis.

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