For the last duplicate ID, retain the date value

SP01 — Wed, 22 Nov 2023 14:49:47 GMT

The problem that I have with my data is this:

id	date_1	date_2	Note
1	6/21/2022	9/13/2022	change to null
1	7/9/2022	9/13/2022	keep this date_2 value
2	10/1/2022
3	10/28/2022	11/19/2022
4	11/13/2022	1/28/2023	change to null
4	12/8/2022	1/28/2023	change to null
4	1/1/2023	1/28/2023	keep this date_2 value
5	12/6/2022	.
5	1/8/2023	.
6	3/4/2023	5/11/2023	change to null
6	3/27/2023	5/11/2023	change to null
4	5/1/2023	5/11/2023	keep this date_2 value

What I want to achieve here is that in case of records having the same IDs and same date_2 values. I want to keep the last occurring ID date_2 value and turn the previous similar ID date_2 values to null.

The dataset I am trying to accomplish should look like this:

id	date_1	date_2
1	6/21/2022	.
1	7/9/2022	9/13/2022
2	10/1/2022
3	10/28/2022	11/19/2022
4	11/13/2022	.
4	12/8/2022	.
4	1/1/2023	1/28/2023
5	12/6/2022	.
5	1/8/2023	.
6	3/4/2023	.
6	3/27/2023	.
4	5/1/2023	5/11/2023

Re: For the last duplicate ID, retain the date value

mkeintz — Wed, 22 Nov 2023 17:34:35 GMT

This is a case that benefits from the use of the BY statement accompanying a SET statement:

data want;
  set have;
  by id  notsorted;
  if last.id=0 then call missing(date_2);
run;

Since data are grouped by ID (but not always in ascending order), I added the NOTSORTED option.

The last.id=0 condition establishes that the observation in hand is NOT the last consecutive obs for the current ID, so the date_2 may be set to missing.

topic For the last duplicate ID, retain the date value in SAS Programming

For the last duplicate ID, retain the date value

Re: For the last duplicate ID, retain the date value