SAS Programming

DATA Step, Macro, Functions and more
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-white.png

Join us for our biggest event of the year!

Four days of inspiring keynotes, product reveals, hands-on learning opportunities, deep-dive demos, and peer-led breakouts. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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
  • 1640 views
  • 1 like
  • 2 in conversation