FIRST. and LAST. in SAS and R: Occurrence Flags and the Sort-Order Trap

By Mohan Peddineni · · 1 min read

FIRST. and LAST. are simple to learn and easy to get wrong. The syntax is never the problem. The problem is that the flag is only as correct as the sort order behind it, and a sort that looks reasonable can still flag the wrong record.

A good place to see this is the ADaM occurrence flags in ADAE: AOCCPFL (first occurrence of each preferred term per subject) and AOCCFL (first adverse event per subject). Below is the same derivation in SAS, dplyr and admiral, plus the mistake that produces a wrong AOCCFL without any error in the log.

How FIRST. and LAST. work

When a DATA step has a BY statement, SAS creates two temporary variables for every BY variable. FIRST.x is 1 on the first record of each BY group and LAST.x is 1 on the last one. They are not written to the output dataset.

A detail that matters: FIRST.x is also set to 1 whenever any BY variable to the left of x changes. With by usubjid aedecod;, FIRST.aedecod is 1 for every new term and for every new subject.

Test data

USUBJIDAESEQAEDECODASTDT
CSP-0011Headache2024-01-12
CSP-0012Nausea2024-01-15
CSP-0013Headache2024-02-03
CSP-0021Nausea2024-01-20
CSP-0022Nausea2024-01-20
CSP-0023Fatigue2024-03-01

CSP-002 has two Nausea records on the same day. That tie is deliberate.

AOCCPFL in SAS

data ae;
  input usubjid :$10. aeseq aedecod :$20. astdt :yymmdd10.;
  format astdt yymmdd10.;
  datalines;
CSP-001 1 Headache 2024-01-12
CSP-001 2 Nausea   2024-01-15
CSP-001 3 Headache 2024-02-03
CSP-002 1 Nausea   2024-01-20
CSP-002 2 Nausea   2024-01-20
CSP-002 3 Fatigue  2024-03-01
;
run;

proc sort data=ae out=ae_pt;
  by usubjid aedecod astdt aeseq;
run;

data adae_pt;
  set ae_pt;
  by usubjid aedecod astdt aeseq;
  length aoccpfl $1;
  if first.aedecod then aoccpfl = 'Y';
run;

The BY statement lists astdt and aeseq even though only FIRST.aedecod is used. That is on purpose. It makes SAS check the sort order, so if someone later changes the PROC SORT, the step stops with an error instead of quietly flagging a different record. aeseq breaks the tie between CSP-002's two Nausea records, so the result is the same every run.

The trap: deriving AOCCFL from the same sort

Since the data is already sorted by subject, it is tempting to add one line:

  if first.usubjid then aoccfl = 'Y';   /* wrong here */

Look at what FIRST.usubjid points to in this sort order:

USUBJIDAEDECODASTDTAOCCPFLFIRST.usubjid
CSP-001Headache2024-01-12Y1
CSP-001Headache2024-02-030
CSP-001Nausea2024-01-15Y0
CSP-002Fatigue2024-03-01Y1
CSP-002Nausea2024-01-20Y0
CSP-002Nausea2024-01-200

For CSP-002 the "first" record is Fatigue on 1 March, because F sorts before N. The subject's actual first event was Nausea on 20 January. For CSP-001 the answer happens to be right, which is exactly why this mistake survives a quick look at the output.

AOCCFL needs its own chronological sort:

proc sort data=adae_pt out=ae_chron;
  by usubjid astdt aeseq;
run;

data adae;
  set ae_chron;
  by usubjid astdt aeseq;
  length aoccfl $1;
  if first.usubjid then aoccfl = 'Y';
run;

Now CSP-002's flag sits on AESEQ = 1, Nausea on 2024-01-20. The rule to remember: one flag, one sort. If two flags need different orders, sort twice.

The same in dplyr

library(dplyr)

ae <- tibble::tribble(
  ~USUBJID,  ~AESEQ, ~AEDECOD,   ~ASTDT,
  "CSP-001", 1,      "Headache", "2024-01-12",
  "CSP-001", 2,      "Nausea",   "2024-01-15",
  "CSP-001", 3,      "Headache", "2024-02-03",
  "CSP-002", 1,      "Nausea",   "2024-01-20",
  "CSP-002", 2,      "Nausea",   "2024-01-20",
  "CSP-002", 3,      "Fatigue",  "2024-03-01"
) %>%
  mutate(ASTDT = as.Date(ASTDT))

adae <- ae %>%
  group_by(USUBJID, AEDECOD) %>%
  arrange(ASTDT, AESEQ, .by_group = TRUE) %>%
  mutate(AOCCPFL = if_else(row_number() == 1, "Y", NA_character_)) %>%
  group_by(USUBJID) %>%
  arrange(ASTDT, AESEQ, .by_group = TRUE) %>%
  mutate(AOCCFL = if_else(row_number() == 1, "Y", NA_character_)) %>%
  ungroup()

row_number() == 1 is the dplyr version of FIRST., and row_number() == n() is LAST.. In R there is no BY statement to check the sort for you, so the arrange() directly before each flag is essential. .by_group = TRUE sorts by the grouping variables first, which keeps the code close to a SAS BY statement.

One more difference from SAS: dplyr::first() returns the first value, not a flag. It is useful for carrying a value across a group, such as the date of the first dose, but it doesn't replace FIRST. for flagging records.

The same in admiral

library(admiral)

adae <- ae %>%
  derive_var_extreme_flag(
    by_vars = exprs(USUBJID, AEDECOD),
    order   = exprs(ASTDT, AESEQ),
    new_var = AOCCPFL,
    mode    = "first"
  ) %>%
  derive_var_extreme_flag(
    by_vars = exprs(USUBJID),
    order   = exprs(ASTDT, AESEQ),
    new_var = AOCCFL,
    mode    = "first"
  )

admiral makes you state the order on every call, which is a good habit to copy in SAS too.

LAST. in practice

LAST. follows the same rules. A common clinical use is keeping one record per subject and parameter, such as the last on-treatment lab value:

proc sort data=lb_ontrt; by usubjid paramcd adt lbseq; run;

data last_val;
  set lb_ontrt;
  by usubjid paramcd adt lbseq;
  if last.paramcd;
run;
last_val <- lb_ontrt %>%
  group_by(USUBJID, PARAMCD) %>%
  arrange(ADT, LBSEQ, .by_group = TRUE) %>%
  slice_tail(n = 1) %>%
  ungroup()

Without lbseq, two records on the last date are a tie and the result depends on input order. Add the tiebreaker and write the rule into the spec, for example "if two results share the latest date, take the one with the highest sequence number".

Counting within groups

The same machinery gives you counts per subject, such as the number of adverse events, without a separate PROC FREQ and merge:

data ae_count;
  set ae_chron;
  by usubjid;
  if first.usubjid then n_event = 0;
  n_event + 1;                 /* sum statement: retained automatically */
  if last.usubjid then output;
  keep usubjid n_event;
run;
ae_count <- ae %>%
  count(USUBJID, name = "n_event")

# or keep every record and add a running event number:
ae_numbered <- ae %>%
  group_by(USUBJID) %>%
  arrange(ASTDT, AESEQ, .by_group = TRUE) %>%
  mutate(event_no = row_number(), n_event = n()) %>%
  ungroup()

The SAS sum statement (n_event + 1;) retains the value across records, and the reset at first.usubjid starts each subject at zero. Forgetting the reset is the classic bug: the count keeps growing across subjects.

Common mistakes

  • Flagging on a variable that isn't in the BY statement. FIRST.x only exists for BY variables.
  • No tiebreaker. Always include a sequence variable such as --SEQ so the result is the same every run.
  • Reusing one sort for two flags. One flag, one sort. Chronological flags need a chronological sort.
  • In R, flagging before sorting. Put arrange() directly before every row_number() flag.
  • Only checking the first subjects. The AOCCFL bug in this post only shows up for some subjects. Check a subject with several events by hand.
Try it yourself: what does this program do?
proc sort data=ae; by usubjid; run;

data adae;
  set ae;
  by usubjid;
  if first.aedecod then aoccpfl = 'Y';
run;

Will AOCCPFL be set correctly? What will the log say?

Answer: AOCCPFL is never set. FIRST.aedecod only exists when aedecod is a BY variable, so here SAS treats it as an ordinary variable that was never assigned. The log shows a note that first.aedecod is uninitialized, and the condition is never true. The fix is to sort and BY on usubjid aedecod astdt aeseq, as shown earlier in this post. It is a good example of why an "uninitialized" note should always be treated as a finding.

Quick check

1. With by usubjid aedecod;, when is FIRST.aedecod equal to 1?

A change in any BY variable to the left also starts a new group, so a new subject resets FIRST.aedecod.

2. Which dplyr expression is the equivalent of LAST.x within a group?

row_number() == n() is TRUE on the last row of each group, provided the data was arranged first.

3. Why add AESEQ to the sort when you only flag on AEDECOD?

Two records with the same term and date are a tie. A sequence variable decides which one comes first, every time.

Key takeaways
  • A FIRST./LAST. flag is only as correct as the sort behind it.
  • Put every sort variable in the BY statement and always include a tiebreaker.
  • One flag, one sort: AOCCPFL and AOCCFL need different orders.
  • In R, row_number() == 1 and row_number() == n() after arrange(). In admiral, state the order in every call.

Related: Study Day (--DY) in SAS and R · More SAS vs R · ADaM articles

MP
Written by Mohan Peddineni

Manager, Statistical Programming. Writes about SAS, R, CDISC and regulatory submissions. More about me → · LinkedIn

↑