topic Re: Remove duplicates with higher values within a variable in SAS Data Management

Remove duplicates with higher values within a variable

kp19 — Mon, 09 Mar 2020 13:31:05 GMT

Hi,

I want to remove duplicates within same by group which have higher value of time difference variable.

The current data:

Obs ID date1 time1 date2 time2 TimeDifference

1 1 03/08/20 11:00 03/08/20 10:55 5

2 1 03/08/20 11:00 03/08/20 10:15 45

3 1 03/08/20 11:00 03/08/20 9:30 90

i want to delete last two observations. I want to do this for multiple IDs with multiple such rows.

Thanks.

Re: Remove duplicates with higher values within a variable

PeterClemmensen — Mon, 09 Mar 2020 13:32:03 GMT

What if two obs have timedif=5?

Re: Remove duplicates with higher values within a variable

kp19 — Mon, 09 Mar 2020 13:33:22 GMT

They don't have same TimeDiff.

Re: Remove duplicates with higher values within a variable

novinosrin — Mon, 09 Mar 2020 13:54:32 GMT

Hi @kp19 Do you mean this?


proc sql;
create table want as
select *
from have
group by id
having TimeDifference=min(TimeDifference);
quit;

Or you could keep it simple by sorting the dataset by id timedifference, and picking the first obs within a by group i.e.

proc sort data=have;
by id timedifference;
run;

data want;
 set have;
 by id;
 if first.id;
run;

Re: Remove duplicates with higher values within a variable

PeterClemmensen — Mon, 09 Mar 2020 13:46:34 GMT

Here is one way. I added another ID for demonstration

data have;
input ID date1 :mmddyy8. time1 :time5. date2 :mmddyy8. time2 :time5. TimeDifference;
format date: mmddyy8. time1 time2 time5.;
datalines;
1 03/08/20 11:00 03/08/20 10:55 5 
1 03/08/20 11:00 03/08/20 10:15 45
1 03/08/20 11:00 03/08/20 9:30  90
2 03/08/20 11:00 03/08/20 10:55 5 
2 03/08/20 11:00 03/08/20 10:15 45
2 03/08/20 11:00 03/08/20 9:30  90
;

data want (drop=minTD);
    minTD = 1e9;
    do _N_=1 by 1 until (last.ID);
        set have;
        by ID;
        if TimeDifference < minTD then minTD = TimeDifference;
    end;
    do _N_=1 to _N_;
        set have;
        if minTD = TimeDifference then output;
    end;
run;

Re: Remove duplicates with higher values within a variable

kp19 — Mon, 09 Mar 2020 14:24:56 GMT

@PeterClemmensen This works well. Thanks a lot

Re: Remove duplicates with higher values within a variable

PeterClemmensen — Mon, 09 Mar 2020 14:27:08 GMT

Anytime 🙂