← SASnRMerging datasets

Introduction

Merging datasets means joining columns — taking two tables that share a key (like USUBJID) and producing a wider table with columns from both sides.
This is how we bring together DM + VS (demographics plus vital signs), or ADSL + ADLB (subject-level plus lab-level data).
SAS does this with a DATA step using MERGE + BY. Tidyverse offers a family of *_join() functions (left_join, inner_join, full_join). Base R uses merge().
This lesson performs a one-to-one merge on USUBJID between two small datasets, producing a combined dataset with the columns of both.
After running, each subject has one row with demographic columns from the first input and the extra columns from the second.

Merging datasets

SAS (Base SAS)+

data SEX;

infile datalines dlm='|' dsd missover;

input Name : $8. Sex : $1.;

label ;

format ;

datalines4;

Alfred|M
Henry|M
Mary|F
;;;;

run;

data AHW;

infile datalines dlm='|' dsd missover;

input Name : $8. Age : best32. Height : best32. Weight : best32.;

label ;

format ;

datalines4;

Alfred|14|69|112.5
Henry|14|63.5|102.5
James|12|57.3|83
;;;;

run;

*------------------------------------------------------------------------------;
*full join;
*------------------------------------------------------------------------------;

data full;

    merge sex ahw;

    by name;

run;

*------------------------------------------------------------------------------;
*left join;
*------------------------------------------------------------------------------;

data left;

    merge sex(in=a) ahw;

    by name;

    if a ;

run;

*------------------------------------------------------------------------------;
*right join;
*------------------------------------------------------------------------------;

data right;

    merge sex ahw(in=b);

    by name;

    if b ;

run;

*------------------------------------------------------------------------------;
*inner join;
*------------------------------------------------------------------------------;

data inner;

    merge sex(in=a) ahw(in=b);

    by name;

    if a and b;

run;

To merge two datasets in SAS, we sort each one by the key variable and then use a DATA step with MERGE + BY.
proc sort data=class; by Name; run; sorts each input on the merge key — SAS requires sorted input for a BY merge.
In the DATA step, merge class class2; by Name; interleaves rows by matching Name values across the inputs.
For matched Name values, SAS writes one combined row with columns from both sides. For Name values in only one input, SAS still writes a row but with missing values for the absent side’s columns (the equivalent of a full outer join).
The default SAS merge is full-outer. To mimic an inner join, we add if in_a and in_b; using the in= dataset options to mark membership.

R (tidyverse)+

library(tidyverse)

sex<-tribble(
~Name,~Sex,
"Alfred","M",
"Henry","M",
"Mary","F",
)

ahw<-tribble(
~Name,~Age,~Height,~Weight,
"Alfred",14,69,112.5,
"Henry",14,63.5,102.5,
"James",12,57.3,83,
)

full<-full_join(sex,ahw,by="Name")

left<-left_join(sex,ahw,by="Name")

right<-right_join(sex,ahw,by="Name")

inner<-inner_join(sex,ahw,by="Name")

In tidyverse, we reach for the join family in dplyr: left_join, inner_join, right_join, full_join.
left_join(class, class2, by = "Name") keeps every row of class and adds matching columns from class2. Non-matching rows get NA for the right-side columns.
inner_join keeps only rows that match in both inputs. full_join keeps every row from either side (equivalent to the default SAS merge).
No sort step is needed — dplyr joins handle unsorted input internally.
The by = argument takes one or more column names. For multi-key joins we pass a vector: by = c("Name", "Visit").

Happy learning!

Search lessons

Introduction

Merging datasets