DATA Step, Macro, Functions and more

Compare values within records of same group

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

Compare values within records of same group

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


Accepted Solutions
Solution
‎05-04-2017 06:49 AM
Respected Advisor
Posts: 4,930

Re: Compare values within records of same group

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;
PG

View solution in original post


All Replies
Solution
‎05-04-2017 06:49 AM
Respected Advisor
Posts: 4,930

Re: Compare values within records of same group

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;
PG
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 209 views
  • 1 like
  • 2 in conversation