Desktop productivity for business analysts and programmers

Removing duplicates rows by max date

Reply
Occasional Contributor
Posts: 10

Removing duplicates rows by max date

I WOULD LIKE TO REMOVE DUPLICATES FROM A VARIABLE KEY TO THE MAXIMUM DATE OF ANOTHER VARIABLE AND I DO NOT KNOW HOW, CAN YOU HELP ME?

Example:

 

COLUMN A(KEY)  COLUMN B(DATE)

1                             20170422

1                             20170423

1                             20170425

 

I NEED THE SAME KEY WITH MAXIMUM DATE, REMOVING THE DUPLICATED KEYS KEEPING THE KEYWORK WITHOUT DUPLICATION FOR THE LAST DATE AVAIABLE TO THIS KEY. CAN YOU UNDERSTAND ME? TKS

PROC Star
Posts: 831

Re: Removing duplicates rows by max date

Posted in reply to PRISCILABRA

proc sql;

select *

from have

group by key

having date=max(date);

quit;/*date is column b*/

Occasional Contributor
Posts: 10

Re: Removing duplicates rows by max date

Posted in reply to novinosrin

Sorry for my answer, thanks a lot for your help.

 

 

Super User
Posts: 6,004

Re: Removing duplicates rows by max date

Posted in reply to PRISCILABRA

SAS contains tools to handle this:

 

proc sort data=have;

by key date;

run;

 

data want;

set have;

by key date;

if last.key;

run;

Super User
Super User
Posts: 8,634

Re: Removing duplicates rows by max date

Posted in reply to PRISCILABRA

Nope, can't understand you, maybe your CapsLock is stuck?  Maybe sort the data in order, then take last or first value?

Ask a Question
Discussion stats
  • 4 replies
  • 275 views
  • 2 likes
  • 4 in conversation