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 structure of the data I have is like below:

id t_date h_date d_date note
1 8/30/2020 . .  
1 8/30/2020 . . keep any one row
2 9/21/2021      
2 9/21/2021 10/15/2021 . Retain only this one
2 10/27/2021 . .

 

this row

3 12/11/2021 . . remove this row 
3 12/11/2021 12/20/2021 1/1/2022 retain this row
4 2/5/2022 . .  
4 3/19/2022 . .  

 

I want to de-duplicate if the rows have same ID, t_date and h_date, d_date is null. But if the rows have same ID & t_date, but either h_date or d_date is not null, I want to keep this row. I want to remove the row with the same ID having both h_date and d_date as null.

What I want is this structure below:

id t_date h_date d_date
1 8/30/2020 . .
2 9/21/2021 10/15/2021 .
2 10/27/2021 . .
3 12/11/2021 12/20/2021 1/1/2022
4 2/5/2022 . .
4 3/19/2022 . .
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Below could work. Code not tested because you didn't provide scripts that create the sample data.

proc sort data=have;
  by id t_date h_date d_date;
run;

data want;
  set have;
  by id t_date h_date d_date;
  if last.t_date;
run;

 

View solution in original post

1 REPLY 1
Patrick
Opal | Level 21

Below could work. Code not tested because you didn't provide scripts that create the sample data.

proc sort data=have;
  by id t_date h_date d_date;
run;

data want;
  set have;
  by id t_date h_date d_date;
  if last.t_date;
run;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 639 views
  • 1 like
  • 2 in conversation