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,
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.