Field based on other variables until next group

sasuser1234567 — Tue, 24 Oct 2023 00:48:31 GMT

I want to create a new field based on existing field if certain conditions are met for a specific group; and keep on assigning it until the next group.

I have the following data (3 columns - ID, Date, RandomInteger) :

ID | Date | RandomInteger

01 | Jul 1, 2000 | 1

01 | Aug 15, 2001 | 0

02 | Aug 30, 2000 | 0

02| Aug 31, 2001 | 2

02 | Sep 30, 2003 | 5

02 | Sep 15, 2005 | 1

02 | Sep 15, 2007 | 0

03| Mar 31, 1999 | 0

An ID can have numerous Dates. RandomInteger can not be negative.

Want:

NewField = RandomInteger

however, if RandomInteger for the group (ID and Date) have exceeded a threshold ( take 3 for example) then NewField is assigned to 3 for subsequent rows that belong to the same ID.

ID | Date | RandomInteger |NewField

01 | Jul 1, 2000 | 1 | 1

01 | Aug 15, 2001 | 0 | 0

02 | Aug 30, 2000 | 0 | 0

02| Aug 31, 2001 | 2 | 2

02 | Sep 30, 2003 | 5 | 3

02 | Sep 15, 2005 | 1 | 3

02 | Sep 15, 2007 | 0 | 3

03| Mar 31, 1999 | 0 | 0

Thanks,

Re: Field based on other variables until next group

whymath — Tue, 24 Oct 2023 02:03:42 GMT

Using an additional flag variable to help:

data have;
  infile cards dlm='|';
  input ID :$2. Date :$12. RandomInteger;
  cards;
01 | Jul 1, 2000  | 1
01 | Aug 15, 2001 | 0
02 | Aug 30, 2000 | 0
02 | Aug 31, 2001 | 2
02 | Sep 30, 2003 | 5
02 | Sep 15, 2005 | 1
02 | Sep 15, 2007 | 0
03 | Mar 31, 1999 | 0
;

data want;
  set have;
  by id;

  retain NewField _3_happend;
  if first.id then call missing(newfield,_3_happend);
  if RandomInteger>3 then _3_happend=1;

  if RandomInteger<=3 and _3_happend=. then NewField=RandomInteger;
  else NewField=3;
run;

topic Field based on other variables until next group in SAS Programming

Field based on other variables until next group

Re: Field based on other variables until next group