Announcement Icon Online training class for Clinical R programming batch starts on Monday, 02Feb2026. Click here for details.

Retain maximum score upto the current observation

Lesson Description
-
  • Sometimes, we want to work with the concept of "Retain maximum score upto the current observation" in a clear, repeatable way.
  • This lesson walks through a simple example and shows the key steps.
  • We will see one approach on how we can do it in SAS and R.

*==============================================================================;
* Create the input dataset;
*==============================================================================;

data pmax;
    input usubjid visitnum score;
    datalines;
101 1 20
101 2 .
101 3 30
102 1 90
102 2 10
102 3 .
102 4 91
102 5 .
;
run;

*==============================================================================;
* Sort the dataset by usubjid and visitnum;
*==============================================================================;

proc sort data=pmax;
    by usubjid visitnum;
run;

*==============================================================================;
* Retain previous non-missing low score;
*==============================================================================;

data pmax01;
    set pmax;
    by usubjid visitnum;

    retain maxscore;

    * Store the original score;
    orig_score = score;

    if first.usubjid then maxscore = .;

    *Store maximum score and assign maxscore when score is missing;
    if not missing(score) then maxscore = max(score,maxscore);
    else score=maxscore;
run;
#==============================================================================;
#Retain previous non-missing high score;
#==============================================================================;

pmax <- tribble(
  ~usubjid,~visitnum,~score,
  101,1,20,
  101,2,NA,
  101,3,30,
  102,1,90,
  102,2,10,
  102,3,NA,
  102,4,91,
  102,5,NA
)


pmax01 <- pmax %>%
  arrange(usubjid,visitnum) %>% 
  group_by(usubjid) %>% 
  mutate(
    orig_score = score,
    temp_score = if_else(is.na(score),-Inf,score),
    maxscore=cummax(temp_score),
    score = if_else(is.na(score),maxscore,score)
  )
pmax <- data.frame(
  usubjid = c(101, 101, 101, 102, 102, 102, 102, 102),
  visitnum = c(1, 2, 3, 1, 2, 3, 4, 5),
  score = c(20, NA, 30, 90, 10, NA, 91, NA)
  , stringsAsFactors = FALSE
)

pmax01 <- pmax[order(pmax$usubjid, pmax$visitnum), ]

pmax01$orig_score <- pmax01$score

pmax01$maxscore <- ave(pmax01$score, pmax01$usubjid, FUN = function(x) {
  temp <- ifelse(is.na(x), -Inf, x)
  cummax(temp)
})

pmax01$score <- ifelse(is.na(pmax01$orig_score), pmax01$maxscore, pmax01$score)
  • cummax() builds a running maximum; NA is treated as -Inf.
  • ifelse() fills missing scores with the running maximum.