How to remove duplicated records\observations WITHOUT sorting in SAS？

Question

I wonder if there is a way to unduplicate records WITHOUT sorting?Sometimes, I want to keep original order and just want to remove duplicated records.

Is it possible?

BTW, below are what I know regarding unduplicating records, which does sorting in the end..

1.

proc sql;
   create table yourdata_nodupe as
   select distinct *
   From abc;
quit;

2.

proc sort data=YOURDATA nodupkey;    
    by var1 var2 var3 var4 var5;    
run;

What's it indexed by currently? I'm sure you could easily retrieve the DB rows in the order you want even after having sorted them in a different way. — , Apr 18 '11 at 03:58

score 16 · Answer 1 · answered Apr 18 '11 at 15:24

You could use a hash object to keep track of which values have been seen as you pass through the data set. Only output when you encounter a key that hasn't been observed yet. This outputs in the order the data was observed in the input data set.

Here is an example using the input data set "sashelp.cars". The original data was in alphabetical order by Make so you can see that the output data set "nodupes" maintains that same order.

data nodupes (drop=rc);;
  length Make $13.;

  declare hash found_keys();
    found_keys.definekey('Make');
    found_keys.definedone();

  do while (not done);
    set sashelp.cars end=done;
    rc=found_keys.check();
    if rc^=0 then do;      
      rc=found_keys.add(); 
      output;              
    end;
  end;
  stop;
run;

proc print data=nodupes;run;

score 1 · Answer 2 · answered Apr 18 '11 at 08:10

/* Give each record in the original dataset and row number */
data with_id ;
  set mydata ;
  _id = _n_ ;
run ;

/* Remove dupes */
proc sort data=with_id nodupkey ;
  by var1 var2 var3 ;
run ;

/* Sort back into original order */
proc sort data=with_id ;
  by _id ;
run ;

score 1 · Answer 3 · answered Apr 18 '11 at 12:23

1

I think the short answer is no, there isn't, at least not a way that wouldn't have a much bigger performance hit than a method based on sorting.

There may be specific cases where this is possible (a dataset where all variables are indexed? A relatively small dataset that you could reasonably load into memory and work with there?) but this wouldn't help you with a general method.

Something along the lines of Chris J's solution is probably the best way to get the outcome you're after, but that's not an answer to your actual question.

answered Apr 18 '11 at 12:23

sasfrog

2,410
1
17
28

2

Yup it's possible - cmjohns answer is correct and does answer the question. It will do exactly as the OP asked, and uses less overhead then sorting. Just make sure the key is the concatenation of all variables and you're good to go. – Robert Penridge Apr 18 '11 at 16:02
You're right Rob, it was @Chris J's answer I was referring to; @cmjohns' nice solution wasn't there when I posted my answer. – sasfrog Apr 18 '11 at 22:12

score 0 · Answer 4 · edited Apr 08 '15 at 22:08

0

Please refer to Usage Note 37581: How can I eliminate duplicate observations from a large data set without sorting, http://support.sas.com/kb/37/581.html . Usage Note 37581 shows how PROC SUMMARY can be used to more efficiently remove duplicates without the use of sorting.

edited Apr 08 '15 at 22:08

Zulu

8,765
9
49
56

answered Apr 08 '15 at 21:40

Kirk Paul Lafler

1

score 0 · Answer 5 · edited Jan 12 '18 at 19:09

The two examples given in the original post are not identical.

distinct in proc sql only removes lines which are fully identical
nodupkey in proc sort removes any line where key variables are identical (even if other variables are not identical). You need the option noduprecs to remove fully identical lines.

If you are only looking for records having common key variables, another solution I could think of would be to create a dataset with only the key variable(s) and find out which one are duplicates and then apply a format on the original data to flag duplicate records. If more than one key variable is present in the dataset, one would need to create a new variable containing the concatenation of all the key variable values - converted to character if needed.

Please don't "sign" your posts. Your profile is automatically added to the post and you can link to your website in your profile. — ChrisF, Jan 12 '18 at 19:10

score 0 · Answer 6 · answered Apr 22 '11 at 17:13

Depending on the number of variables in your data set, the following might be practical:

data abc_nodup;
   set abc;
   retain _var1 _var2 _var3 _var4;
   if _n_ eq 1 then output;
   else do;
      if (var1 eq _var1) and (var2 eq _var2) and
         (var3 eq _var3) and (var4 eq _var4)
         then delete;
      else output;
   end;
   _var1 = var1;
   _var2 = var2;
   _var3 = var3;
   _var4 = var4;
   drop _var:;
run;

score -1 · Answer 7 · answered May 08 '13 at 00:40

This is the fastest way I can think of. It requires no sorting.

data output_data_name;
    set input_data_name (
        sortedby = person_id stay
        keep =
            person_id
            stay
            ... more variables ...);
    by person_id stay;
    if first.stay > 0 then output;
run;

score -1 · Answer 8 · answered Sep 13 '14 at 04:13

-1

data output;
set yourdata;
by var notsorted;
if first.var then output;
run;

This will not sort the data but will remove duplicates within each group.

answered Sep 13 '14 at 04:13

Ash Datti

1

How to remove duplicated records\observations WITHOUT sorting in SAS？

8 Answers8

Linked