topic retain variable untill higher value is encountered in SAS Programming

retain variable untill higher value is encountered

chd1425 — Fri, 08 Dec 2023 15:45:15 GMT

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

Re: retain variable untill higher value is encountered

yabwon — Fri, 08 Dec 2023 15:49:36 GMT

retain AETOXGR_base .;

AETOXGR_base = max(AETOXGR_base, AETOXGR);

Re: retain variable untill higher value is encountered

Tom — Fri, 08 Dec 2023 15:50:45 GMT

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;

Re: retain variable untill higher value is encountered

ballardw — Fri, 08 Dec 2023 16:49:23 GMT

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.