Full Join

Merge Joins

Overview

MERGE is used to combine observations from two or more datasets horizontally (adding columns/variables from different sources). The behaviour depends on the relationship between the datasets based on the BY variable(s).

⚠ Behavior Difference

SAS merge in DATA step shows max(Rows_left, Rows_right) and the log will have the note "merge by repeats" if the rows are not unique as per the BY variables. Whereas R performs a full join and gives a Cartesian product, and also throws a warning "Detected an unexpected many-to-many relationship between x and y" unless the relationship is specified as many-to-many. Also, R merges without sorting the datasets by the BY variables.

Syntax Comparison

SAS

proc sort data=LEFT;
  by SUBJID;
run;

proc sort data=RIGHT;
  by SUBJID;
run;

data FULLJOIN;
  merge LEFT RIGHT;
  by SUBJID;
run;

library(dplyr)

FULLJOIN <- full_join(LEFT, RIGHT, by = "SUBJID")

Details

Full Join is used when you need to retain all records from both datasets, regardless of whether matching records exist. It returns all observations from both tables, combining matched records where possible and displaying missing values when no match is found. In clinical programming, it is commonly used when merging two different datasets to ensure that all records from both sources are preserved.

Example 1 — Deriving AESTDY and AEENDYExample 1

We have 2 datasets: DM (Demographics) on the left and AE (Adverse Events) on the right. To calculate the duration of AEs from the start of treatment, we merge both datasets and derive 2 new columns: AESTDY and AEENDY.

Input Datasets


── Input Dataset: DM ──────────────────────────────

SUBJID    RFSTDTC
1015      2013-12-01
1023      2012-05-01
1028      2013-06-01


── Input Dataset: AE ──────────────────────────────

SUBJID    AETERM                      AESTDTC      AEENDTC
1015      APPLICATION SITE ERYTHEMA   2014-01-03   .
1015      APPLICATION SITE PRURITUS   2014-01-03   .
1015      DIARRHOEA                   2014-01-09   2014-01-11
1023      ERYTHEMA                    2012-08-07   .
1023      ERYTHEMA                    2012-08-07   2012-08-30
1028      APPLICATION SITE ERYTHEMA   2013-07-21   .
1028      APPLICATION SITE PRURITUS   2013-08-08   .

SAS Code

SAS

proc sort data=DM;
  by SUBJID;
run;

proc sort data=AE;
  by SUBJID;
run;

data ADAE;
  merge DE AE;
  by SUBJID;
  if AESTDTC not in ('' ' ') and RFSTDTC not in ('' ' ') then
    AESTDY = input(AESTDTC, yymmdd10.) - input(RFSTDTC, yymmdd10.) + 1;
  if AEENDTC not in ('' ' ') and RFSTDTC not in ('' ' ') then
    AEENDY = input(AEENDTC, yymmdd10.) - input(RFSTDTC, yymmdd10.) + 1;
run;

R Code

library(dplyr)

ADAE <- full_join(DEMO, ADAE_, by = "SUBJID") %>%
  mutate(
    AESTDY = case_when(
      !is.na(AESTDTC) & !is.na(RFSTDTC) ~
        as.numeric(as.Date(AESTDTC) - as.Date(RFSTDTC)) + 1
    ),
    AEENDY = case_when(
      !is.na(AEENDTC) & !is.na(RFSTDTC) ~
        as.numeric(as.Date(AEENDTC) - as.Date(RFSTDTC)) + 1
    )
  )

Output Dataset


SUBJID    RFSTDTC      AETERM                      AESTDTC      AEENDTC      AESTDY   AEENDY
1015      2013-12-01   APPLICATION SITE ERYTHEMA   2014-01-03   .            34       .
1015      2013-12-01   APPLICATION SITE PRURITUS   2014-01-03   .            34       .
1015      2013-12-01   DIARRHOEA                   2014-01-09   2014-01-11   40       42
1023      2012-05-01   ERYTHEMA                    2012-08-07   .            99       .
1023      2012-05-01   ERYTHEMA                    2012-08-07   2012-08-30   99       122
1028      2013-06-01   APPLICATION SITE ERYTHEMA   2013-07-21   .            51       .
1028      2013-06-01   APPLICATION SITE PRURITUS   2013-08-08   .            69       .

📝 Note: The output dataset is the same in both SAS and R. In SAS, the input() function converts character dates to numeric before calculating the day difference. In R, as.Date() converts the character date and as.numeric() converts the result. Both produce identical AESTDY and AEENDY values.

Example 2 — Many-to-Many Merge BehaviorExample 2

This example shows the key difference between SAS and R when merging datasets that have multiple observations for the same subject. Datasets AE and CM are merged by SUBJID.

Input Datasets


── Input Dataset: AE ──────────────────────────────

SUBJID    AETERM                      AESTDTC      AEENDTC
1015      APPLICATION SITE ERYTHEMA   2014-01-03   .
1015      APPLICATION SITE PRURITUS   2014-01-03   .
1015      DIARRHOEA                   2014-01-09   2014-01-11
1023      ERYTHEMA                    2012-08-07   .
1023      ERYTHEMA                    2012-08-07   2012-08-30
1023      ERYTHEMA                    2012-08-07   2012-08-30
1023      ATRIOVENTRICULAR BLOCK      2012-08-26   .
1028      APPLICATION SITE ERYTHEMA   2013-07-21   .
1028      APPLICATION SITE PRURITUS   2013-08-08   .


── Input Dataset: CM ──────────────────────────────

SUBJID    CMTRT           CMSTDTC
1015      PARACETAMOL     2013-12-15
1015      IBUPROFEN       2014-01-02
1023      METFORMIN       2012-05-10
1023      ATORVASTATIN    2012-07-20
1028      OMEPRAZOLE      2013-06-01
1028      SALBUTAMOL      2013-07-15
1033      AMLODIPINE      2014-02-05
1033      LOSARTAN        2014-03-10
1057      LEVOTHYROXINE   2015-01-20
1057      VITAMIN D       2015-04-01

SAS Code

SAS

data AECM;
  merge AE CM;
  by SUBJID;
run;

/* To get Cartesian product like R, use PROC SQL */
proc sql;
  create table merged_cartesian as
  select COALESCE(AE.SUBJID, CM.SUBJID) as SUBJID,
         AE.AETERM,
         AE.AESTDTC,
         AE.AEENDTC,
         CM.CMTRT,
         CM.CMSTDTC
  from AE
  full join CM
  on AE.SUBJID = CM.SUBJID;
quit;

R Code

library(dplyr)

# Basic full join (throws many-to-many warning)
AECM <- full_join(AE, CM, by = "SUBJID")

# Suppress warning by specifying relationship
AECM <- full_join(AE, CM, by = "SUBJID",
                  relationship = "many-to-many")

Output Dataset


── SAS Output — max(rows_left, rows_right) per subject ────

SUBJID    AETERM                      AESTDTC      AEENDTC      CMTRT           CMSTDTC
1015      APPLICATION SITE ERYTHEMA   2014-01-03   .            PARACETAMOL     2013-12-15
1015      APPLICATION SITE PRURITUS   2014-01-03   .            IBUPROFEN       2014-01-02
1015      DIARRHOEA                   2014-01-09   2014-01-11   IBUPROFEN       2014-01-02
1023      ERYTHEMA                    2012-08-07   .            METFORMIN       2012-05-10
1023      ERYTHEMA                    2012-08-07   2012-08-30   ATORVASTATIN    2012-07-20
1023      ERYTHEMA                    2012-08-07   2012-08-30   ATORVASTATIN    2012-07-20
1023      ATRIOVENTRICULAR BLOCK      2012-08-26   .            ATORVASTATIN    2012-07-20
1028      APPLICATION SITE ERYTHEMA   2013-07-21   .            OMEPRAZOLE      2013-06-01
1028      APPLICATION SITE PRURITUS   2013-08-08   .            SALBUTAMOL      2013-07-15
1033      .                           .            .            AMLODIPINE      2014-02-05
1033      .                           .            .            LOSARTAN        2014-03-10
1057      .                           .            .            LEVOTHYROXINE   2015-01-20
1057      .                           .            .            VITAMIN D       2015-04-01



── R Output — Full Cartesian product (22 rows) ────────────

SUBJID    AETERM                      AESTDTC      AEENDTC      CMTRT           CMSTDTC
1015      APPLICATION SITE ERYTHEMA   2014-01-03   .            PARACETAMOL     2013-12-15
1015      APPLICATION SITE ERYTHEMA   2014-01-03   .            IBUPROFEN       2014-01-02
1015      APPLICATION SITE PRURITUS   2014-01-03   .            PARACETAMOL     2013-12-15
1015      APPLICATION SITE PRURITUS   2014-01-03   .            IBUPROFEN       2014-01-02
1015      DIARRHOEA                   2014-01-09   2014-01-11   PARACETAMOL     2013-12-15
1015      DIARRHOEA                   2014-01-09   2014-01-11   IBUPROFEN       2014-01-02
1023      ERYTHEMA                    2012-08-07   .            METFORMIN       2012-05-10
1023      ERYTHEMA                    2012-08-07   .            ATORVASTATIN    2012-07-20
1023      ERYTHEMA                    2012-08-07   2012-08-30   METFORMIN       2012-05-10
1023      ERYTHEMA                    2012-08-07   2012-08-30   ATORVASTATIN    2012-07-20
1023      ERYTHEMA                    2012-08-07   2012-08-30   METFORMIN       2012-05-10
1023      ERYTHEMA                    2012-08-07   2012-08-30   ATORVASTATIN    2012-07-20
1023      ATRIOVENTRICULAR BLOCK      2012-08-26   .            METFORMIN       2012-05-10
1023      ATRIOVENTRICULAR BLOCK      2012-08-26   .            ATORVASTATIN    2012-07-20
1028      APPLICATION SITE ERYTHEMA   2013-07-21   .            OMEPRAZOLE      2013-06-01
1028      APPLICATION SITE ERYTHEMA   2013-07-21   .            SALBUTAMOL      2013-07-15
1028      APPLICATION SITE PRURITUS   2013-08-08   .            OMEPRAZOLE      2013-06-01
1028      APPLICATION SITE PRURITUS   2013-08-08   .            SALBUTAMOL      2013-07-15
1033      .                           .            .            AMLODIPINE      2014-02-05
1033      .                           .            .            LOSARTAN        2014-03-10
1057      .                           .            .            LEVOTHYROXINE   2015-01-20
1057      .                           .            .            VITAMIN D       2015-04-01

📝 Note: SAS produces max(rows_left, rows_right) = 3 rows for SUBJID 1015 and logs: NOTE: MERGE statement has more than one data set with repeats of BY values. R produces a Cartesian product = 3 x 2 = 6 rows for SUBJID 1015 and throws a warning.

Conclusion

Key Takeaway: • SAS DATA step MERGE → gives max rows per subject (sequential pairing) • R full_join() → gives Cartesian product (all combinations) • To get the same Cartesian result as R in SAS → use PROC SQL full join • Always check the SAS log for the MERGE repeat note when working with non-unique BY values