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.
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: