← SASnRLEAD: Access the Next Row's Value

Introduction

Sometimes we need the value from the next row rather than the previous one — forward-looking comparisons like time to next event, next visit's score, or duration until a future milestone.
SAS does not have a native LEAD function. The common workaround is to sort the data in descending order and use LAG on the reversed data, or merge a dataset to itself with an offset key.
R tidyverse provides dplyr::lead(), which is the mirror image of lag() and works naturally inside group_by().
Base R builds the shifted vector with c(tail(x, -1), NA) — dropping the first element and appending NA at the end.
We use the same 3-subject, 3-visit dataset to show all three approaches side by side.

LEAD: Access the Next Row's Value

SAS (Base SAS)+

*==============================================================================;
* Sample data: 3 subjects, 3 visits each, with numeric AVAL scores
*==============================================================================;

data advs;
  infile datalines dlm='|' dsd missover;
  input USUBJID : $7. VISITNUM PARAMCD : $6. AVAL;
  datalines4;
101-001|1|SYSBP|130
101-001|2|SYSBP|125
101-001|3|SYSBP|120
101-002|1|SYSBP|145
101-002|2|SYSBP|140
101-002|3|SYSBP|138
101-003|1|SYSBP|118
101-003|2|SYSBP|122
101-003|3|SYSBP|119
;;;;
run;

*==============================================================================;
* Approach 1: Sort descending by visit, LAG on the reversed data
*==============================================================================;
* When we sort VISITNUM descending, the "previous" row in that order is
* actually the next visit in chronological order.
*------------------------------------------------------------------------------;

proc sort data=advs out=advs_desc;
  by USUBJID descending VISITNUM;
run;

data lead_desc;
  set advs_desc;
  by USUBJID descending VISITNUM;
  next_aval = lag(AVAL);
  if first.USUBJID then next_aval = .;
run;

proc sort data=lead_desc;
  by USUBJID VISITNUM;
run;

proc print data=lead_desc noobs;
run;

*==============================================================================;
* Approach 2: Merge dataset to itself with an offset visit number
*==============================================================================;
* We create a lookup copy where VISITNUM is decremented by 1, then merge
* on USUBJID and VISITNUM. Each row picks up the AVAL from the next visit.
*------------------------------------------------------------------------------;

proc sort data=advs;
  by USUBJID VISITNUM;
run;

data next_visit;
  set advs(rename=(AVAL=next_aval));
  VISITNUM = VISITNUM - 1;
  keep USUBJID VISITNUM next_aval;
run;

proc sort data=next_visit;
  by USUBJID VISITNUM;
run;

data lead_merge;
  merge advs(in=a) next_visit;
  by USUBJID VISITNUM;
  if a;
run;

proc print data=lead_merge noobs;
run;

SAS has no built-in LEAD function, so we work around it. Two common approaches are shown here.
In Approach 1, we sort ADVS by USUBJID and descending VISITNUM. Now visit 3 comes first, then 2, then 1 within each subject.
We call lag(AVAL) on this reversed data. Because the rows are backwards, "previous" in processing order is actually the next visit chronologically.
We blank out next_aval on first.USUBJID (which is the last chronological visit after descending sort) to avoid cross-subject leakage.
We then re-sort by ascending VISITNUM to restore the natural order.
In Approach 2, we create a lookup dataset where each row's VISITNUM is decremented by 1 and AVAL is renamed to next_aval. Merging this back onto the original by USUBJID and VISITNUM pairs each row with the AVAL from one visit ahead.
The last visit of each subject has no match in the lookup, so next_aval is missing — exactly what we want.
Approach 2 is more flexible when visits are not sequential integers — we can join on actual visit dates or visit names instead.

R (tidyverse)+

library(dplyr)

# ==============================================================================
# Sample data: 3 subjects, 3 visits each, with numeric AVAL scores
# ==============================================================================

advs <- tibble(
  USUBJID  = rep(c("101-001", "101-002", "101-003"), each = 3),
  VISITNUM = rep(1:3, times = 3),
  PARAMCD  = "SYSBP",
  AVAL     = c(130, 125, 120, 145, 140, 138, 118, 122, 119)
)

# ==============================================================================
# lead() inside group_by — straightforward
# ==============================================================================

result <- advs %>%
  arrange(USUBJID, VISITNUM) %>%
  group_by(USUBJID) %>%
  mutate(
    next_aval = lead(AVAL),
    diff_next = next_aval - AVAL
  ) %>%
  ungroup()

print(result)

We load dplyr and build the same ADVS tibble with 3 subjects and 3 visits.
arrange(USUBJID, VISITNUM) ensures visits are in chronological order within each subject.
group_by(USUBJID) scopes the lead operation to each subject — it will not pull a value from the next subject.
lead(AVAL) inside mutate() returns the AVAL from the next row within the group. The last row of each subject gets NA automatically.
We compute diff_next = next_aval - AVAL to see how much the score changes at the next visit. Rows where next_aval is NA produce NA.
lead() accepts an optional n argument to look further ahead: lead(AVAL, 2) would skip one row and pull from two positions forward.
After running, we confirm that VISITNUM 3 for each subject shows NA for next_aval, and that earlier visits show the correct forward-looking value.

Happy learning!

Search lessons

Introduction

LEAD: Access the Next Row's Value