Hello,
I have a data set where each record is identified by a record id. Multiple records share a similar unique id based on identifying individuals. Some records with same unique id have different dates.
Could I identify the record with the unique id that has the earliest date by creating a flag variable with 0 (for no) and 1 (for yes) values?
sample code below:
data have;
input recordID uniqueid date:ddmmyy10.;
format date ddmmyy10.;
datalines;
1234625 55 08/03/2006
1234632 72 06/03/2005
1234634 8 11/11/1997
1234636 104 09/03/2007
1234650 88 12/10/1999
1234617 55 05/12/2000
;
run;
data want;
input recordID uniqueid date:ddmmyy10. flag;
format date ddmmyy10.;
datalines;
1234625 55 08/03/2006 0
1234632 72 06/03/2005 0
1234634 8 11/11/1997 0
1234636 104 09/03/2007 0
1234650 88 12/10/1999 0
1234617 55 05/12/2000 1
;
run;
Thank you!
data have;
input recordID uniqueid date:ddmmyy10.;
format date ddmmyy10.;
datalines;
1234625 55 08/03/2006
1234632 72 06/03/2005
1234634 8 11/11/1997
1234636 104 09/03/2007
1234650 88 12/10/1999
1234617 55 05/12/2000
;
run;
proc sort data=have out=temp;
by uniqueid date;
run;
data want;
set temp;
by uniqueid;
if first.uniqueid and last.uniqueid then want=0;
else want=ifn(first.uniqueid,1,0);
run;
data have;
input recordID uniqueid date:ddmmyy10.;
format date ddmmyy10.;
datalines;
1234625 55 08/03/2006
1234632 72 06/03/2005
1234634 8 11/11/1997
1234636 104 09/03/2007
1234650 88 12/10/1999
1234617 55 05/12/2000
;
run;
proc sort data=have out=temp;
by uniqueid date;
run;
data want;
set temp;
by uniqueid;
if first.uniqueid and last.uniqueid then want=0;
else want=ifn(first.uniqueid,1,0);
run;
I think this would do the same:
want = ifn(first.uniqueid and not last.uniqueid,1,0);
or, done with IF:
if first.uniqueid and not last,uniqueid
then want = 1;
else want = 0;
To get the want dataset as shown in the original question, an additional sort by recordid is required.
Thanks Kurt! This works great as well!
Thank you so much! This worked great!
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.