When a findings domain carries --LOBXFL, build it in R as: filter qualifying rows, take the last date per entity, join "Y" back, fill the rest with "". Do not flag in place with a running last..
RFXSTDTC is first study treatment. RFSTDTC is the subject reference start and may differ. --DTC is character ISO. Convert to Date and compare VSDT to RFXSTDT.
Quick reference
library(dplyr)vs <- vs |>mutate(VSDT =as.Date(substr(VSDTC, 1, 10)), # ISO character --DTC -> DateRFXSTDT =as.Date(substr(RFXSTDTC, 1, 10)) )last_before <- vs |>filter(!is.na(VSDT), !is.na(RFXSTDT), !is.na(VSSTRESN), VSDT <= RFXSTDT) |>group_by(USUBJID, VSTESTCD) |>slice_max(VSDT, n =1, with_ties =TRUE) |>ungroup() |>distinct(USUBJID, VSTESTCD, VSDT) |>mutate(VSLOBXFL ="Y")vs |>left_join(last_before, by =c("USUBJID", "VSTESTCD", "VSDT")) |>mutate(VSLOBXFL =coalesce(VSLOBXFL, ""))
R
SAS
filter(VSDT <= RFXSTDT)
subset on or before RFXSTDTC
slice_max(VSDT, n = 1, with_ties = TRUE)
last date in that subset
left_join(...) then coalesce(..., "")
merge a "Y" flag; blank otherwise
Traps: group by the entity (VSTESTCD here), not only the subject. Drop missing numeric results (!is.na(VSSTRESN)) before picking last. An empty qualifying set stays blank, not "Y" and not NA. with_ties = TRUE flags every row that shares the last date; distinct() on the flag keys so the join does not multiply rows. Same calendar day as first dose with no times needs a study rule; <= is one choice. The flag is "", not NA. Compare Date columns (VSDT, RFXSTDT), not mixed-precision --DTC strings.
Worked examples below.
The example table
Two subjects, two vital-sign tests. RFXSTDTC is on the row as character ISO.
library(dplyr)
Attaching package: 'dplyr'
The following objects are masked from 'package:stats':
filter, lag
The following objects are masked from 'package:base':
intersect, setdiff, setequal, union
STD-001-0001 SYSBP: last on or before 2025-06-03 with a result is 2025-06-03 (same calendar day as first dose)
STD-001-0001 DIABP: 2025-06-02 has no numeric result, so it does not qualify; two rows share 2025-05-20 and both get the flag
STD-001-0002 SYSBP: both dates are after 2025-06-10; nothing qualifies
Filter, slice_max(), join a "Y"
Drop missing dates and missing numeric results (!is.na(VSSTRESN)), then keep VSDT <= RFXSTDT. Per USUBJID and VSTESTCD, take the latest VSDT. Distinct the keys so a tied date does not multiply rows on the join. Flag "Y", join back, fill the rest with "".
STD-001-0001 SYSBP on 2025-06-03 is "Y". Both STD-001-0001 DIABP rows on 2025-05-20 are "Y" (with_ties = TRUE). The DIABP row with a missing numeric result stays blank. Post-baseline rows are blank. STD-001-0002 is blank on both rows.
These dates have no times. A visit on the same calendar day as first dose cannot be ordered by datetime. The study rule must say whether that day counts as before, after, or is decided by visit or timepoint. The pattern above treats same calendar day as on or before (<=).
ImportantBlank is "", not NA
--LOBXFL is "Y" or blank. A left join puts NA on unmatched rows. waldo and a SAS XPT compare treat NA versus "" as a difference on every unflagged row. coalesce(VSLOBXFL, "") is required for that compare.
Group by the entity
Last-before-exposure is per test here (VSTESTCD), not per subject. group_by(USUBJID) alone selects one date for the subject and can flag the wrong DIABP row, or miss it. Put the finding key in group_by().
Empty qualifying set stays blank
STD-001-0002 has no row with VSDT <= RFXSTDT. last_before has no keys for that subject. The left join matches nothing; coalesce(..., "") leaves both rows blank. Do not assign "Y" to the earliest post-baseline record.
with_ties = TRUE
slice_max(..., with_ties = TRUE) keeps every row that shares the maximum date. When two records on the same entity fall on the last qualifying day, both get "Y".
distinct(USUBJID, VSTESTCD, VSDT) makes the right-hand table unique on the join keys, so those two left rows each match once. Without distinct(), a two-row tie on the right cartesian-joins against two left rows with the same keys.
with_ties = FALSE keeps one row and does not specify which. If the study requires exactly one flag per entity on a tied date, pass a tie-breaker to slice_max() (a sequence or a datetime).