Sequence numbers

–SEQ within a subject

SAS: PROC SORT then _N_ within BY, --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 IG order.

Quick reference

library(dplyr)

df |>
  arrange(USUBJID, TUEVAL, TULNKID, VISITNUM, TUDY) |>  # order first
  group_by(USUBJID) |>
  mutate(SEQ = as.double(row_number())) |>              # --SEQ, XPT-shaped
  ungroup()

R	SAS
arrange(...) then row_number()	PROC SORT then _N_ within BY
group_by(USUBJID)	BY USUBJID
as.double(row_number())	SAS numeric is always double

Three 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(). Integer SEQ is not how SAS XPT stores it; use as.double().

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. IG order: 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. Equivalent to _N_ without PROC SORT.

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.

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 IG 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 IG order: TUEVAL, TULNKID, VISITNUM, TUDY. If a tie remains, add a source sequence or a datetime.

as.double() for XPT

SAS numeric is double. row_number() returns integer. haven / xportr write that as integer unless it is cast. SDTM --SEQ in a SAS XPT is double:

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

[1] "double"

double. Cast before writing XPT so the type matches SAS.