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
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.