## Groups

Super Contributor
Posts: 713

# Groups

id    pin
C1    P1
C1    P2
C3    P3
C4    P4
C5    P4

if the id's are same and pin are different, then group= "Same-Id,Diff-Pin"
if id's are different and have same pin , then group= "Same-Pin,Diff-Id"

id    pin   group
C1    P1    Same-Id,Diff-Pin
C1    P2    Same-Id,Diff-Pin
C3    P3    Other
C4    P4    Same-Pin,Diff-Id
C5    P4    Same-Pin,Diff-Id

Posts: 1,270

## Re: Groups

proc sql;
create table want as
select *,count(id) as cnt_id from have
group by id;
quit;

proc sql;
create table final as
select *,count(pin) as cnt_pin from want
group by pin;
quit;

proc sql;
select id,pin,case
when cnt_id>1 and cnt_pin=1 then 'Same-Id, Diff-Pin'
when cnt_id=1 and cnt_pin=1 then 'Other'
when cnt_id=1 and cnt_pin>1 then 'Same-Pin, Diff-Id'
end as group
from final
order by id,pin;
quit;

Occasional Contributor
Posts: 7

## Re: Groups

data y;

input id \$ pin \$;

cards;

C1    P1

C1    P2

C3    P3

C4    P4

C5    P4

C6   P5

C7    P6

C7    P7

C9    P7

;

run;

data y;

set y;

by id pin;

if first.id =last.id then a=0;

else a+1;

if a ne 0 then a=1;

else a=a;

run;

proc sort data=y;

by pin a;

run;

data y;

set y;

by pin a;

if first.pin =last.pin then b=0;

else b+1;

if b ne 0 then b=1;

else b=b;

if a=1 and b=0 then group = "Same-Id,Diff-Pin";

if a=0 and b=0 then group = "Other";

if a=0 and b=1 then group = "Same-Pin,Diff-Id";

if a=1 and b=1 then group = "Other?";

drop a b;

run;

Super User
Posts: 8,127

## Re: Groups

You need to make sure that your input does not have multiple rows for the same ID*PIN combination for these queries to work.

proc sql ;

create table want as

select id,pin,'Same-Id,Diff-Pin' as group

from have

group by id

having count(*)> 1

union

select id,pin,'Same-Pin,Diff-Id' as group

from have

group by pin

having count(*)> 1

union

select id,pin,'Other ' as group

from (select id,pin

from have

group by id

having count(*)= 1

)

group by pin

having count(*)=1

order by 1,2,3

;

quit;

Discussion stats
• 3 replies
• 287 views
• 0 likes
• 4 in conversation