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 |
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.