BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BenBrady
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
shantanupl1
Obsidian | Level 7

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

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
s_lassen
Meteorite | Level 14

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;
shantanupl1
Obsidian | Level 7

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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