topic Re: Removing the first observation of duplicates in SAS Programming

Removing the first observation of duplicates

michan22 — Fri, 12 Oct 2018 19:50:20 GMT

Hi SAS experts,

My dataset contains duplicate observations and the second observation of the duplicate contains information I need for some variables.

I have sorted the data by 2 variables: record_id and event_name, and by using proc sort with nodupkey SAS deletes the second observation of the duplicate.

Is there a way for me to keep the second observation and delete the first of the duplicates?

Thank you in advance for any advice!!

Re: Removing the first observation of duplicates

novinosrin — Fri, 12 Oct 2018 19:51:48 GMT

Can you post input and required output samples plz

Re: Removing the first observation of duplicates

michan22 — Fri, 12 Oct 2018 19:59:16 GMT

Currently my dataset has:

record_id event_name instrument vital performance symptoms ...........

1 consult 1

1 3 months follow up

1 3 months follow up 1 80 1

1 6 months follow up 1

1 1 year follow up 1 80 0

There are about 5000 observations in my dataset and a lot of record has duplicates like record 1, and I would like to keep the second observation of the duplicate as it has data values in variables I am interested.

Re: Removing the first observation of duplicates

novinosrin — Fri, 12 Oct 2018 20:02:51 GMT

assuming you want to keep the second of each by group i.e record_id

data want;
do _n_=1 by 1 until(last.record_id);
set have;
by record_id;
if _n_=2 then output;
end;
run;
/*or/And what if you had just one record_idin some cases*/
data want;
do _n_=1 by 1 until(last.record_id);
set have;
by record_id;
if first.record_id and last.record_id then output;
else if _n_=2 then output;
end;
run;

Re: Removing the first observation of duplicates

PGStats — Sat, 13 Oct 2018 04:34:47 GMT

BY processing can be put to profit here

data want;
set have;
by record_id event_name;

/* To keep only the last record */
if last.event_name;

/* OR */
/* To remove only the first record when there are many */
*if not first.event_name or last.event_name;

run;

Re: Removing the first observation of duplicates

michan22 — Sat, 13 Oct 2018 23:45:27 GMT

Apologies but I should have specified that I would like to keep the second record of the 3 month in my example. So each line would be a different event_name.

I would like to have:

record_id event_name instrument vital performance symptoms ...........

1 consult 1

1 3 months follow up 1 80 1

1 6 months follow up 1

1 1 year follow up 1 80 0

Re: Removing the first observation of duplicates

PGStats — Sat, 13 Oct 2018 23:54:50 GMT

That's what my code does:

data have;
input record_id event_name :&$12. instrument :&$12. vital performance symptoms;
datalines;
1                    .                      consult                  1  . .                     
1                    3 months               follow up    . . . 
1                    3 months               follow up            1                 80                             1
1                    6 months               follow up            1 . .                                                  
1                    1 year                 follow up            1                  80                            0
;

proc sort data=have; by record_id event_name; run;

data want;
set have;
by record_id event_name;

/* To keep only the last record */
if last.event_name;

/* OR */
/* To remove only the first record when there are many */
*if not first.event_name or last.event_name;

run;

proc print; run;

           record_     event_
    Obs       id        name      instrument    vital    performance    symptoms

     1        1                   consult         1            .            .
     2        1       1 year      follow up       1           80            0
     3        1       3 months    follow up       1           80            1
     4        1       6 months    follow up       1            .            .

Re: Removing the first observation of duplicates

Patrick — Sun, 14 Oct 2018 00:07:55 GMT

@michan22

For the data you've posted the following should work (which is basically what @PGStats already suggested).

data have;
  infile datalines truncover dsd dlm='|' ;
  input record_id event_name :$20. instrument vital performance symptoms;
  datalines;
1|consult|1|
1|3 months|follow up 
1|3 months|follow up|1|80|1
1|6 months|follow up|1|
1|1 year|follow up|1|80|0
;
run;

proc sort data=have out=inter;
  by record_id event_name vital performance symptoms;
run;

data want;
  set inter;
  by record_id event_name;
  if last.event_name;
run;

Question is if the sample data you've posted is sufficiently representative for your real data. I.e. if the record you want to keep has VITAL as missing but there is a "duplicate" with Vital populated then above code wouldn't work.

Also if you need logic which just consolidates all non-missing data per record_id and event_name into a single record then the code logic needs also to be different.

Re: Removing the first observation of duplicates

michan22 — Sun, 14 Oct 2018 00:20:42 GMT

Thank you!!! It worked! I was stuck on this for a few days and didn't want to try to delete the first duplicate manually. It's amazing how the coding is just 2 lines!

Re: Removing the first observation of duplicates

Patrick — Sun, 14 Oct 2018 00:28:10 GMT

@PGStats

I believe you need to sort by at least one additional variable as else if the source data comes in a different order you can easily end-up picking the records with the missings - which I believe is not what the OP wants.

Re: Removing the first observation of duplicates

michan22 — Sun, 14 Oct 2018 00:49:13 GMT

I see! Thank you for the wonderful advice!! I will add an additional variable in proc sort

Re: Removing the first observation of duplicates

PGStats — Sun, 14 Oct 2018 02:48:01 GMT

I understand your point @Patrick, but there might not be another variable to sort by. However there should be no need for an extra sorting variable as the default sorting behavior is to keep the original ordering within by-groups (see the SORTEQUALS option.)