Within-subject sequence

row_number()

SAS: PROC SORT then first. and a retained counter, --SEQ

--SEQ is a within-subject row number after a defined order. In R: arrange(), then group_by(USUBJID), then row_number(). Without arrange(), the numbers follow the current row order, not the study keys.

Quick reference

library(dplyr)

df |>
  arrange(USUBJID, TUEVAL, TULNKID, VISITNUM, TUDY) |>  # order first
  group_by(USUBJID) |>
  mutate(SEQ = as.double(row_number())) |>              # --SEQ; double for SAS compares
  ungroup()
R SAS
arrange(...) then group_by(USUBJID) then row_number() PROC SORT then if first.usubjid then seq = 0; seq + 1;
group_by(USUBJID) BY USUBJID
as.double(row_number()) SAS numeric is double

Traps: row_number() follows the current row order, so skip arrange() and --SEQ is whatever the last PROC left behind. Equal dates need tie-breakers in arrange(). _N_ is not a within-BY counter. Cast to double so waldo/diffdf match SAS numeric; XPT has no separate integer type.

Worked examples below.

The example table

Tumor results in input order, which is not the TUSEQ order.

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
tu <- data.frame(
  USUBJID  = c(
    "STD-001-0001", "STD-001-0001", "STD-001-0001",
    "STD-001-0001", "STD-001-0002"
  ),
  TUEVAL   = c(
    "INVESTIGATOR", "INVESTIGATOR", "INVESTIGATOR",
    "INDEPENDENT ASSESSOR", "INVESTIGATOR"
  ),
  TULNKID  = c("T01", "T01", "T02", "T01", "T01"),
  VISITNUM = c(3, 1, 1, 1, 2),
  TUDY     = c(15, 1, 1, 1, 8),
  TUSTRESC = c("SD", "CR", "CR", "CR", "PR")
)

tu
       USUBJID               TUEVAL TULNKID VISITNUM TUDY TUSTRESC
1 STD-001-0001         INVESTIGATOR     T01        3   15       SD
2 STD-001-0001         INVESTIGATOR     T01        1    1       CR
3 STD-001-0001         INVESTIGATOR     T02        1    1       CR
4 STD-001-0001 INDEPENDENT ASSESSOR     T01        1    1       CR
5 STD-001-0002         INVESTIGATOR     T01        2    8       PR

STD-001-0001 has investigator lesions T01 and T02, and one independent-assessor row. VISITNUM 3 is the first input row. Sponsor order used here: evaluator, lesion, visit, study day.

Wrong: row_number() without arrange()

tu |>
  group_by(USUBJID) |>
  mutate(TUSEQ = row_number()) |>
  ungroup()
# A tibble: 5 × 7
  USUBJID      TUEVAL               TULNKID VISITNUM  TUDY TUSTRESC TUSEQ
  <chr>        <chr>                <chr>      <dbl> <dbl> <chr>    <int>
1 STD-001-0001 INVESTIGATOR         T01            3    15 SD           1
2 STD-001-0001 INVESTIGATOR         T01            1     1 CR           2
3 STD-001-0001 INVESTIGATOR         T02            1     1 CR           3
4 STD-001-0001 INDEPENDENT ASSESSOR T01            1     1 CR           4
5 STD-001-0002 INVESTIGATOR         T01            2     8 PR           1

STD-001-0001 is numbered 1 to 4 in file order. VISITNUM 3 is TUSEQ 1. SAS BY usubjid on unsorted data stops with BY variables are not properly sorted. That check is only USUBJID, not the secondary keys. R does not error. This example is already in USUBJID order, so SAS would also number it in file order without an error.

Right: arrange(), then number

proc sort data=tu;
  by usubjid tueval tulnkid visitnum tudy;
run;

data tu;
  set tu;
  by usubjid;
  if first.usubjid then tuseq = 0;
  tuseq + 1;
run;
tu |>
  arrange(USUBJID, TUEVAL, TULNKID, VISITNUM, TUDY) |>
  group_by(USUBJID) |>
  mutate(TUSEQ = as.double(row_number())) |>
  ungroup()
# A tibble: 5 × 7
  USUBJID      TUEVAL               TULNKID VISITNUM  TUDY TUSTRESC TUSEQ
  <chr>        <chr>                <chr>      <dbl> <dbl> <chr>    <dbl>
1 STD-001-0001 INDEPENDENT ASSESSOR T01            1     1 CR           1
2 STD-001-0001 INVESTIGATOR         T01            1     1 CR           2
3 STD-001-0001 INVESTIGATOR         T01            3    15 SD           3
4 STD-001-0001 INVESTIGATOR         T02            1     1 CR           4
5 STD-001-0002 INVESTIGATOR         T01            2     8 PR           1

INDEPENDENT ASSESSOR sorts before INVESTIGATOR and is TUSEQ 1. Then investigator T01 visit 1, T01 visit 3, T02 visit 1. TULNKID precedes VISITNUM, so T01 visit 3 precedes T02 visit 1. STD-001-0002 restarts at 1.

_N_ does not restart within BY. The SAS analogue of group_by(USUBJID) then row_number() is the first. plus retained counter above, not _N_.

Importantrow_number() numbers the current order

It does not infer a clinical order. group_by(USUBJID) then row_number() uses the frame's current order. Sort first. Include every study key in arrange(), including tie-breakers.

Tie-breakers

Two rows can share a study day. arrange(USUBJID, TUDY) alone leaves their relative order undefined. Add keys that make a row unique, in the sponsor-defined order: TUEVAL, TULNKID, VISITNUM, TUDY. If a tie remains, add a source sequence or a datetime.

as.double() for R-side compares

XPT has numeric and character only. Integer is not a distinct XPT type. row_number() returns integer in R. SAS numeric is double. waldo and diffdf report that as a type difference even when the values match. Cast when you need the R objects to compare equal:

tu |>
  arrange(USUBJID, TUEVAL, TULNKID, VISITNUM, TUDY) |>
  group_by(USUBJID) |>
  mutate(TUSEQ = as.double(row_number())) |>
  ungroup() |>
  pull(TUSEQ) |>
  typeof()
[1] "double"

double. The cast is for R-side compares against SAS numeric, not because XPT stores a separate integer type.