BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SP01
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

1 REPLY 1
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 670 views
  • 1 like
  • 2 in conversation