topic Re: Deleting duplicates based on multiple criteria in SAS Programming

Deleting duplicates based on multiple criteria

BenBrady — Wed, 21 Jun 2017 01:41:00 GMT

I want to delete and keep observations based on multiple conditions. For example, in the following dataset I want to do the following:

If there is duplicates of a person i want to keep the one where score1=score2. If this still leave duplicates of that person then I want the person with the lower year (i.e 2015 not 2016). If there are duplicates for a person but score1 is not equal to score2 for any of them then I want to keep the one with the lower year. I have illustrated how i want the original data set to look below:

Person	Score1	Score2	Year
A	15	15	2015
A	15	15	2016
A	15	12	2016
B	8	7	2015
C	10	10	2016
D	11	12	2015
D	11	13	2016


Person	Score1	Score2	Year
A	15	15	2015
B	8	7	2015
C	10	10	2016
D	11	12	2015

Note that if there is no duplicates for a person then I want to keep them regardless of whether score1=score2.

Re: Deleting duplicates based on multiple criteria

Jagadishkatam — Wed, 21 Jun 2017 03:13:36 GMT

Pease try the sql

data have;
input Person$ Score1	Score2	Year;
cards;
A	15	15	2015
A	15	15	2016
A	15	12	2016
B	8	7	2015
C	10	10	2016
D	11	12	2015
D	11	13	2016
;

proc sql;
create table want as select * from (select *, count(*) as count, 
case when count(*)>1 and score1=score2 then 1
when count(*)>1 and score1^=score2 then 2 else count(*) end as flag from have 
group by person,year ) group by person having min(year)=year and min(flag)=flag;
quit;

Re: Deleting duplicates based on multiple criteria

BenBrady — Wed, 21 Jun 2017 02:54:38 GMT

Thanks! How would I change the SAS code to make it applicable to an exisiting data set with the same variables that have thousands of observations?

Re: Deleting duplicates based on multiple criteria

Jagadishkatam — Wed, 21 Jun 2017 03:15:46 GMT

I hope the dataset has the same variables liek person, score1,score2 and year. if that is the case then replace the dataset name with your dataset, like replace have in the below code with your dataset name. this will create the dataset want.

proc sql;
create table want as select * from (select *, count(*) as count, 
case when count(*)>1 and score1=score2 then 1
when count(*)>1 and score1^=score2 then 2 else count(*) end as flag from have 
group by person,year ) group by person having min(year)=year and min(flag)=flag;
quit;