DATA Step, Macro, Functions and more

Removing duplicates by satisfying a frequency condition

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Removing duplicates by satisfying a frequency condition

I have a data set which is much larger than my example below. I want to be able to remove the 'Person' duplicates by the 'type' variable. In particular, if they have the same 'Type', then can keep either. However, if they have different values for 'Type', i want to keep the record with the higher frequency. In this case it would be one with Type=2 (as there is 2 of them compared to only one record where Type=1). How would I go about doing this?

 

 

 

 

PersonType
A2
A2
A1
B3
B3

Accepted Solutions
Solution
‎04-16-2018 07:16 PM
Occasional Contributor
Posts: 5

Re: Removing duplicates by satisfying a frequency condition

Try this.

proc sql;
create table want1 as
select *, count(*) as n from have group by person, type order by person, calculated n desc;
run;

data want;
set want1;
by person descending n;
if first.person;
drop n;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,400

Re: Removing duplicates by satisfying a frequency condition

like this?

 

data have;
input Person$ Type;
datalines;
A 2
A 2
A 1
B 3
B 3
;

proc sort data=have;
    by descending Person descending Type;
run;

data want;
    set have;
    by descending Person descending Type;
    if first.Person;
run;
PROC Star
Posts: 269

Re: Removing duplicates by satisfying a frequency condition

Here is one way to do it, using SQL:

proc sql;
  create table want as select * from
   (select * from
     (select *,count(*) as n from have
      group  by person,type
     )
   group by person
   having n=max(n)
   )
  group by person
  having type=min(type);

quit;
Solution
‎04-16-2018 07:16 PM
Occasional Contributor
Posts: 5

Re: Removing duplicates by satisfying a frequency condition

Try this.

proc sql;
create table want1 as
select *, count(*) as n from have group by person, type order by person, calculated n desc;
run;

data want;
set want1;
by person descending n;
if first.person;
drop n;
run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 121 views
  • 1 like
  • 4 in conversation