topic Re: Groups in SAS Programming

Groups

SASPhile — Thu, 17 Jul 2014 15:24:27 GMT

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

Re: Groups

stat_sas — Thu, 17 Jul 2014 16:04:38 GMT

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;

Re: Groups

jimbobob — Thu, 17 Jul 2014 19:26:30 GMT

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;

Re: Groups

Tom — Fri, 18 Jul 2014 04:58:47 GMT

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;