Groups

[SASPhile]

  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

[stat_sas]

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;

[jimbobob]

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;

[Tom]

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;