retain variable untill higher value is encountered

[chd1425]

Hi, 

I would like to retain the AETOXGR variable as AETOXGR_base untill AETOXGR is greater than the previous AETOXGR_base. The problem is that the AETOXGR_base should not change even if the previous AETOXGR was higher, but not higher than another previous AETOXGR. The data should be sorted by SubjectKey and startdate. 

Example: 

DATA have: 

__SUBJECTKEY	STARTDATE	AETOXGR
1	01/Jan/23	1
1	02/Jan/23	2
1	03/Jan/23	1
1	04/Jan/23	3
1	05/Jan/23	1
1	06/Jan/23	2

DATA want:

__SUBJECTKEY	STARTDATE	AETOXGR	AETOXGR_base
1	01/Jan/23	1	1
1	02/Jan/23	2	2
1	03/Jan/23	1	2
1	04/Jan/23	3	3
1	05/Jan/23	1	3
1	06/Jan/23	2	3

[yabwon]

retain AETOXGR_base .;

AETOXGR_base = max(AETOXGR_base, AETOXGR);

[Tom]

Use a RETAIN statement.

data want;
  set have;
  by subjectkey startdate;
  retain aetoxgr_max ;
  if first.subjectkey then aetoxgr_max =.;
  aetoxgr_max =max(aetoxgr_max,aetoxgr);
run;

[ballardw]

Isn't it amazing that when the question is about retaining a value that SAS has a Retain statement to address that need? 👍

Perhaps a note that this is not available in Proc SQL is appropriate just in case someone tries it with that approach.