Compare values within records of same group

[Elliott]

I need to compare transactional data with the ID group. I want to keep in the dataset if the values of type are the same within the records with the same id. Any records that do not match the type at least twice within the id grouping should be dropped.

If I have this data:

data have;

input id $4. type $2. Code $1.;

datalines;

abc hb y

abc hb e

abc mb w

abc mb u

abc cc y

cba mb b

cba hb y

bac hb n

bac hb a

def cc y

def cc x

def cc w

;

run;

desired output dataset should contain:

abc hb y

abc hb e

abc mb w

abc mb u

bac hb n

bac hb a

def cc y

def cc x

def cc w

I just don’t know how to do an iterative compare with multiple records.

Hope this makes sense.

Thank you

[PGStats · Accepted solution]

data have;
input id $4. type :$2. Code :$1.;
datalines;
abc hb y
abc hb e
abc mb w
abc mb u
abc cc y
cba mb b
cba hb y
bac hb n
bac hb a
def cc y
def cc x
def cc w
;

proc sql;
create table want as
select *
from have
group by id, type
having count(*) > 1;
select * from want;
quit;