topic Re: Remove duplicated dates based on an Index value in New SAS User

Remove duplicated dates based on an Index value

NewUsrStat — Wed, 19 Jun 2024 12:30:35 GMT

Hi guys,

suppose to have the following:

data DB;
input ID :$20. Admission :date09. Discharge :date09. Index1;
format Admission date9. Discharge date9.;
cards;
0001 13JAN2017 25JAN2017 0 
0001 22FEB2018 03MAR2018 0 
0001 30JAN2019 04MAR2019 0 
0001 30JAN2019 04MAR2019 1 
0001 25DEC2020 02JAN2021 0 
0002 15JAN2014 29JAN2014 1 
0002 01FEB2015 10FEB2015 0 
0002 01FEB2015 10FEB2015 0 
0003 22MAR2019 23MAR2019 1 
0003 22MAR2019 23MAR2019 0
0003 02JUL2019 28AUG2019 0
0004 01SEP2022 15SEP2022 1
0004 02DEC2023 05DEC2023 1
;run;

Is there a way to get the following? In other words I need to remove duplicated rows (identical admission-discharge date) and retain the inly one where Index1 = 1. I tried with proc sort to order by the date and the Index1 (descending) without success. Thank you in advance!

data DB1;
input ID :$20. Admission :date09. Discharge :date09. Index1;
format Admission date9. Discharge date9.;
cards;
0001 13JAN2017 25JAN2017 0 
0001 22FEB2018 03MAR2018 0 
0001 30JAN2019 04MAR2019 1 
0001 25DEC2020 02JAN2021 0 
0002 15JAN2014 29JAN2014 1 
0002 01FEB2015 10FEB2015 0 
0003 22MAR2019 23MAR2019 1   
0003 02JUL2019 28AUG2019 0              
0004 01SEP2022 15SEP2022 1            
0004 02DEC2023 05DEC2023 1
;run;

Re: Remove duplicated dates based on an Index value

PaigeMiller — Wed, 19 Jun 2024 09:46:21 GMT

May we assume there is exactly one record which has index=1 for duplicated rows? Is this record with index=1 always the second record of the duplicated records? Are there ever triplicates or quadruplicates?

Re: Remove duplicated dates based on an Index value

NewUsrStat — Wed, 19 Jun 2024 12:27:35 GMT

I just edited the post with all possible cases. Thank you very much!

Re: Remove duplicated dates based on an Index value

Kurt_Bremser — Thu, 20 Jun 2024 15:29:17 GMT

proc sort data=db;
by id admission discharge index1;
run;

data db1;
set db;
by id admission discharge;
if last.discharge.
run;

Re: Remove duplicated dates based on an Index value

PaigeMiller — Thu, 20 Jun 2024 17:02:20 GMT

@NewUsrStat wrote:
I just edited the post with all possible cases. Thank you very much!

While that works in this case, in general we want data and the proper output (which you have provided), but also an explanation in words so we don't have to take our time to figure out what is happening and possibly getting it wrong. You already know, please share what you know with us. Please in the future, tell us what the logic is to go from input data to output data.