topic Re: Remove duplicates and keep last entry only in New SAS User

Remove duplicates and keep last entry only

mmh — Thu, 18 Nov 2021 09:50:36 GMT

Dear Altruists.
I am a new SAS user.
I have the following dataset:

GVKey Report_Date Quarter
1001 31-03-21 Q1
1001 30-06-21 Q2
1001 30-09-21 Q3
1001 05-10-21 Q3
1001 10-10-21 Q3
1001 31-12-21 Q4
1002 31-03-21 Q1
1002 30-06-21 Q2
1002 01-07-21 Q2
1002 30-09-21 Q3
1002 31-12-21 Q4

I want to remove the duplicates and keep only the last entry based on the variable "Quarter" for a given firm.
I want two resulting datasets. The first is a clean dataset as follows:

GVKey Report_Date Quarter
1001 31-03-21 Q1
1001 30-06-21 Q2
1001 10-10-21 Q3
1001 31-12-21 Q4
1002 31-03-21 Q1
1002 01-07-21 Q2
1002 30-09-21 Q3
1002 31-12-21 Q4

The second dataset will contain only the duplicate values as follows:

GVKey Report_Date Quarter
1001 30-09-21 Q3
1001 05-10-21 Q3
1002 30-06-21 Q2

I thank you in advance for your kind support!

Re: Remove duplicates and keep last entry only

AndreaVianello — Thu, 18 Nov 2021 13:25:38 GMT

data have;
input GVKey $ RD $ Quarter$;
infile datalines dlm = ' ';
Report_date=mdy( substr(rd,4,2) , substr(rd,1,2) , substr(rd,7,2) );
format Report_date ddmmyy10.;
datalines;
1001 31-03-21 Q1
1001 30-06-21 Q2
1001 30-09-21 Q3
1001 05-10-21 Q3
1001 10-10-21 Q3
1001 31-12-21 Q4
1002 31-03-21 Q1
1002 30-06-21 Q2
1002 01-07-21 Q2
1002 30-09-21 Q3
1002 31-12-21 Q4
; run;

data out_last out_dup;
set have;
by GVKey Quarter;
if last.quarter=1 then output out_last;
else output out_dup;
run;

Re: Remove duplicates and keep last entry only

Kurt_Bremser — Thu, 18 Nov 2021 13:42:32 GMT

Since the FIRST. and LAST. automatic variables contain only boolean values (1=true, 0=false), you can use last.quarter on its own without comparing it with 1.

if last.quarter
then output out_last;
else output out_dup;

Re: Remove duplicates and keep last entry only

mmh — Fri, 19 Nov 2021 01:44:31 GMT

Thanks a lot Andrea!
It was really kind of you 🙂