Master the Art of Merging SAS Datasets: A Step-by-Step Guide

Merging datasets is one of the most fundamental skills for any SAS programmer. Whether you’re working in clinical trials, finance, or data science, you’ll often find your data scattered across multiple tables. To gain meaningful insights, you need to bring them together horizontally.

In this post, we’ll walk through the essential steps of merging datasets in SAS, ensuring your data alignment is perfect every time.

The Core Concept: Horizontal Combination

Unlike “stacking” (which adds observations vertically), merging combines variables from two or more datasets based on a common key variable. Think of it like a puzzle: you’re matching pieces from different boxes to complete one picture.

The 3 Golden Rules of SAS Merging

Before you write a single line of MERGE code, you must follow these rules:

1. Identify the Key: You need at least one common variable (like SubjectID, EmpID, or Date) that exists in all datasets you want to merge.
2. Sort Your Data: SAS requires that all input datasets are sorted by the key variable(s).
3. Use a BY Statement: Without a BY statement, SAS will perform a “one-to-one” merge, which is rarely what you want and can lead to dangerous data errors.

Step-by-Step Implementation

Step 1: Sort the Datasets

First, use PROC SORT to prepare your data.

proc sort data=demographics;
    by SubjectID;
run;

proc sort data=vitals;
    by SubjectID;
run;

Step 2: The DATA Step Merge

Now, we use the MERGE statement and the BY statement together.

data study_combined;
    merge demographics vitals;
    by SubjectID;
run;

Advanced Control with the IN= Option

Sometimes you don’t want every record. You might only want subjects who appear in both datasets (an Inner Join). This is where the IN= data set option becomes your best friend.

data matching_only;
    merge demographics(in=a) vitals(in=b);
    by SubjectID;
    if a and b; /* Only keep if Subject exists in both */
run;

Summary

Merging in SAS is powerful but requires discipline. Always remember to Sort, Merge, and BY. By mastering these steps, you can handle even the most complex data structures with confidence.

---

Ready to dive deeper into SAS programming? Check out our other guides on proc-sql and data-preparation!