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

In data analytics, real-world data is rarely stored in a single table. In clinical trials, patient demographics, adverse events, lab measurements, and vital signs live in separate datasets.

To build meaningful reports, you must bring these tables together horizontally.

1. The Core Concept: Horizontal Merge vs. Vertical Stacking

Before writing code, visualize how data moves:

• Stacking (SET statement): Adds observations vertically (increases row count).
• Merging (MERGE statement): Adds variables horizontally by aligning rows on a shared key variable (like SubjectID or EmployeeID).

2. The 3 Golden Rules of SAS Merging

Before writing a single line of MERGE code, memorize the 3 Golden Rules:

1. Identify the Shared Key: You must have at least one common variable (e.g., SubjectID, EmpID) present in all tables with matching data types.
2. Sort Every Dataset First: SAS requires all input datasets to be presorted by the BY variables.
3. Always Include the BY Statement: Merging without a BY statement triggers a sequential one-to-one merge, which causes dangerous record misalignment.

3. Step-by-Step Code Walkthrough

Let's look at real clinical trial data with demographics and vitals:

/* Step 1: Sort Both Datasets by the Key Variable */
proc sort data=demographics;
    by SubjectID;
run;

proc sort data=vitals;
    by SubjectID;
run;

/* Step 2: Combine using DATA Step MERGE */
data combined_study_data;
    merge demographics vitals;
    by SubjectID;
run;

4. Advanced Control with the IN= Option (Venn Logic)

By default, SAS performs a Full Outer Join. The IN= option gives you precise control over which records to keep:

/* 1. Inner Join: Keep only subjects with complete records in both tables */
data study_inner_join;
    merge demographics(in=a) vitals(in=b);
    by SubjectID;
    if a and b; /* Keep only when both flags are 1 */
run;

/* 2. Left Join: Keep all demographics, pull vitals when available */
data study_left_join;
    merge demographics(in=a) vitals(in=b);
    by SubjectID;
    if a; /* Keep all master demographics */
run;

/* 3. Data Integrity Audit: Find patients missing clinical vitals */
data missing_vitals_audit;
    merge demographics(in=a) vitals(in=b);
    by SubjectID;
    if a and not b; /* Enrolled subjects who missed visit */
run;

"The golden test: Always check your SAS Log after every merge to confirm record counts and ensure no unexpected duplicates."