DATA Step, Macro, Functions and more

Deleting duplicates based on multiple criteria

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Deleting duplicates based on multiple criteria

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:

 

PersonScore1Score2Year
A15152015
A15152016
A15122016
B872015
C10102016
D11122015
D11132016
    
    
PersonScore1Score2Year
A15152015
B872015
C10102016
D11122015

 

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


Accepted Solutions
Solution
‎06-21-2017 12:15 AM
Trusted Advisor
Posts: 1,137

Re: Deleting duplicates based on multiple criteria

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;
Thanks,
Jag

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: Deleting duplicates based on multiple criteria

[ Edited ]

 

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;
Thanks,
Jag
Occasional Contributor
Posts: 15

Re: Deleting duplicates based on multiple criteria



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?


Solution
‎06-21-2017 12:15 AM
Trusted Advisor
Posts: 1,137

Re: Deleting duplicates based on multiple criteria

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;
Thanks,
Jag
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 195 views
  • 3 likes
  • 2 in conversation