Eliminate duplicates

[qchac]

Hello.

I have this table :

http://img.photobucket.com/albums/v622/qchac/table.jpg

and I want to eliminate all the duplicate values and to preserve only the record with the most recent date_def or the weekest cli for each duplicate group.
-> For example, in this table, from the first duplicate (rows 1, 2) will remain only row 1.

What is the SAS solution for this ?

Thx in advance.
Raul

 

[ChrisW75]

Hiya qchac - That's a nice easy one. I'm afraid I can't really see your pic very well, it's a bit small, however this is what I do when I'm deduping.

proc sort data=inpt;
  by key_var1 key_var2 date_ref;
run;

data outpt;
  set inpt;
  by key_var1 key_var2;

  if last.key_var2;
run;

Alternatively, and I prefer this method for the ability to to reporting afterwards and examin the duplicates, change the second step to:-

data outpt
     dupes;
  set inpt;
  by key_var1 key_var2;

  if last.key_var2 then output outpt;
  if not(first.key_var2 and last.key_var2) 
       then output dupes;
run;

This will put all the duplicates in a separate file (master records and slave records) which you can look at afterwards for data quality control).

If you have a different number of key variables (the variables that you use to determine uniqueness) just adjust the code accordingly, always use the last of the key variables as the one that you test.
For information on LAST. and FIRST., check out the SAS doco site, and that'll fill you in on the various things that you can do with it.
Enjoy.

 

[qchac]

Thx a lot Chris !