Hi all ,
I am trying to flag the duplicate records over the group. same id fall under different groups. i need to flg only those records who fall under different groups
have:
data have;
input (id grp pam) (: $8.) seq val ord 8.;
cards;
100 xyz pop 1 10 1.1
100 xyz pop 2 11 1.2
100 xyz pop 3 12 1.3
100 xyz pop 4 15 1.4
100 xyz pop 5 11 1.5
100 xyz pop 6 13 1.6
100 xyz pop 7 30 1.7
100 xyz pop 8 40 1.8
100 xyz pop 9 10 1.9
100 xyz pop 10 30 1.10
100 abc pop 9 10 1.9
100 abc pop 10 30 1.10
100 abc pop 9 10 1.11
100 abc pop 9 10 1.11
100 abc pop 12 30 1.12
100 abc pop 13 40 1.13
100 abc pop 14 20 1.14
100 abc pop 15 10 1.15
100 abc pop 16 40 1.16
100 abc pop 17 10 1.17
;
run;
want:
id grp pam seq val ord flg
100 xyz pop 1 10 1.10
100 xyz pop 2 11 1.20
100 xyz pop 3 12 1.30
100 xyz pop 4 15 1.40
100 xyz pop 5 11 1.50
100 xyz pop 6 13 1.60
100 xyz pop 7 30 1.70
100 xyz pop 8 40 1.80
100 xyz pop 9 10 1.90 1
100 xyz pop 10 30 1.10 1
100 abc pop 9 10 1.90 1
100 abc pop 10 30 1.10 1
100 abc pop 9 10 1.11
100 abc pop 9 10 1.11
100 abc pop 12 30 1.12
100 abc pop 13 40 1.13
100 abc pop 14 20 1.14
100 abc pop 15 10 1.15
100 abc pop 16 40 1.16
100 abc pop 17 10 1.17
my code:
proc sql; create table want as select a.*,b.flg from have as a left join(select id,pam,seq,val,ord,1 as flg from have group by id,pam,seq,val,ord having count(*)>1) as b on (a.id=b.id & a.pam=b.pam & a.seq=b.seq & a.val=b.val & a.ord=b.ord); quit;
my code is flagging the duplicate record, if id fall in single group.
Thanks
... View more
