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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: