topic Re: Identifying the record with the earliest date. in New SAS User

Identifying the record with the earliest date.

Ihsan-Mahdi — Wed, 13 Mar 2024 05:19:49 GMT

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!

Re: Identifying the record with the earliest date.

Ksharp — Wed, 13 Mar 2024 05:46:11 GMT

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;

Re: Identifying the record with the earliest date.

Kurt_Bremser — Wed, 13 Mar 2024 08:16:55 GMT

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.

Re: Identifying the record with the earliest date.

Ihsan-Mahdi — Wed, 13 Mar 2024 14:25:07 GMT

Thank you so much! This worked great!

Re: Identifying the record with the earliest date.

Ihsan-Mahdi — Wed, 13 Mar 2024 14:26:22 GMT

Thanks Kurt! This works great as well!