How to check for double registration and double mortgage

[andrew roder]

Dear All,

Can you all help me with the following :

How to check for double registration :

Tag all cases where the Identifier_Type and Vehicle_No is the same but the registrationID is different and the Agency is same

How to check for double mortgage:

Tag all cases where the Identifier_Type and Vehicle_No is the same but the registrationID is different and the Agency is different

sample of my data

copy starting from the next line --------- ---------- --

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 Agency str14 RegistrationNo str22 Identifier_Type str13    Vehicle_No
"BDBL"  "R2016100017850" "Earth Moving Equipment" "BP-1-A0001"
"BNB"   "R2016080016682" "Earth Moving Equipment" "BP-1-A0002"
"BNB"   "R2016100017768" "Earth Moving Equipment" "BP-1-A0006"
"BNB"   "R2016090017481" "Earth Moving Equipment" "BP-1-A0009"
"RICBL" "R2017040019953" "Earth Moving Equipment" "BP-1-A0010"
"RICBL" "R2019020038511" "Earth Moving Equipment" "BP-1-A0011"
"BOB"   "R2016090017344" "Earth Moving Equipment" "BP-1-A0012"
"BNB"   "R2017080022003" "Earth Moving Equipment" "BP-1-A0013"
"BIL"   "R2017020019114" "Earth Moving Equipment" "BP-1-A0016"
"BNB"   "R2016090017512" "Earth Moving Equipment" "BP-1-A0017"
"RICBL" "R2016100017922" "Earth Moving Equipment" "BP-1-A0018"
"BDBL"  "R2016100017684" "Earth Moving Equipment" "BP-1-A0018"
"BNB"   "R2017020019341" "Earth Moving Equipment" "BP-1-A0019"
"BNB"   "R2017030019724" "Earth Moving Equipment" "BP-1-A0020"
"BNB"   "R2017030019723" "Earth Moving Equipment" "BP-1-A0021"
"RICBL" "R2016090017376" "Earth Moving Equipment" "BP-1-A0022"
"RICBL" "R2016090017412" "Earth Moving Equipment" "BP-1-A0023"
"RICBL" "R2017030019629" "Earth Moving Equipment" "BP-1-A0028"
"BNB"   "R2017020019255" "Earth Moving Equipment" "BP-1-A0029"
"DPNB"  "R2018070030102" "Earth Moving Equipment" "BP-1-A0030"
"RICBL" "R2016100017788" "Earth Moving Equipment" "BP-1-A0031"
"RICBL" "R2016100017772" "Earth Moving Equipment" "BP-1-A0032"
"BDBL"  "R2016080016524" "Earth Moving Equipment" "BP-1-A0034"
"BNB"   "R2017080022035" "Earth Moving Equipment" "BP-1-A0049"
"RICBL" "R2018050029405" "Earth Moving Equipment" "BP-1-A0049"
"RICBL" "R2017030019380" "Earth Moving Equipment" "BP-1-A0051"
"BNB"   "R2017060020994" "Earth Moving Equipment" "BP-1-A0054"
"BDBL"  "R2021020050601" "Earth Moving Equipment" "BP-1-A0055"
"RICBL" "R2016110018079" "Earth Moving Equipment" "BP-1-A0057"
"BNB"   "R2017080021664" "Earth Moving Equipment" "BP-1-A0062"
"BNB"   "R2017080021663" "Earth Moving Equipment" "BP-1-A0063"
"BIL"   "R2017050020664" "Earth Moving Equipment" "BP-1-A0064"
"BDBL"  "R2017080022027" "Earth Moving Equipment" "BP-1-A0066"
"DPNB"  "R2017010019027" "Earth Moving Equipment" "BP-1-A0068"
"BNB"   "R2017100026671" "Earth Moving Equipment" "BP-1-A0069"
"BNB"   "R2017100026672" "Earth Moving Equipment" "BP-1-A0070"
"RICBL" "R2017020019129" "Earth Moving Equipment" "BP-1-A0072"
"DPNB"  "R2017010019028" "Earth Moving Equipment" "BP-1-A0074"
"BNB"   "R2017010018936" "Earth Moving Equipment" "BP-1-A0075"
"RICBL" "R2018100033636" "Earth Moving Equipment" "BP-1-A0077"
"BOB"   "R2016120018534" "Earth Moving Equipment" "BP-1-A0078"
"BNB"   "R2017030019594" "Earth Moving Equipment" "BP-1-A0079"
"BNB"   "R2018080031295" "Earth Moving Equipment" "BP-1-A0079"
"BIL"   "R2016120018715" "Earth Moving Equipment" "BP-1-A0080"
"BIL"   "R2017020019108" "Earth Moving Equipment" "BP-1-A0081"
"BIL"   "R2016120018715" "Earth Moving Equipment" "BP-1-A0081"
"BNB"   "R2017110026996" "Earth Moving Equipment" "BP-1-A0082"
"BNB"   "R2017110026997" "Earth Moving Equipment" "BP-1-A0083"
"BDBL"  "R2016120018541" "Earth Moving Equipment" "BP-1-A0084"
"BNB"   "R2017030019426" "Earth Moving Equipment" "BP-1-A0087"
"BNB"   "R2017070021611" "Earth Moving Equipment" "BP-1-A0088"
"BOB"   "R2017030019866" "Earth Moving Equipment" "BP-1-A0089"
"BOB"   "R2017040019888" "Earth Moving Equipment" "BP-1-A0090"
"BOB"   "R2017050020603" "Earth Moving Equipment" "BP-1-A0091"
"BNB"   "R2017030019592" "Earth Moving Equipment" "BP-1-A0092"
"BNB"   "R2017070021626" "Earth Moving Equipment" "BP-1-A0093"
"BIL"   "R2016120018730" "Earth Moving Equipment" "BP-1-A0094"
"RICBL" "R2017030019754" "Earth Moving Equipment" "BP-1-A0095"
"BDBL"  "R2019100043520" "Earth Moving Equipment" "BP-1-A0096"
"RICBL" "R2017040020030" "Earth Moving Equipment" "BP-1-A0097"
"RICBL" "R2017120027380" "Earth Moving Equipment" "BP-1-A0098"
"RICBL" "R2017030019816" "Earth Moving Equipment" "BP-1-A0099"
"BNB"   "R2017040020299" "Earth Moving Equipment" "BP-1-A0101"
"BNB"   "R2020060046843" "Earth Moving Equipment" "BP-1-A0101"
"RICBL" "R2017090022185" "Earth Moving Equipment" "BP-1-A0103"
"RICBL" "R2017080022059" "Earth Moving Equipment" "BP-1-A0105"
"BIL"   "R2017030019370" "Earth Moving Equipment" "BP-1-A0108"
"BNB"   "R2017040020231" "Earth Moving Equipment" "BP-1-A0109"
"BNB"   "R2017040020200" "Earth Moving Equipment" "BP-1-A0111"
"BNB"   "R2017040020178" "Earth Moving Equipment" "BP-1-A0112"
"BNB"   "R2022050041746" "Earth Moving Equipment" "BP-1-A0112"
"RICBL" "R2021030051567" "Earth Moving Equipment" "BP-1-A0113"
"BNB"   "R2017080021754" "Earth Moving Equipment" "BP-1-A0114"
"BNB"   "R2017040020093" "Earth Moving Equipment" "BP-1-A0115"
"TBANK" "R2017050020406" "Earth Moving Equipment" "BP-1-A0116"
"TBANK" "R2016060015772" "Earth Moving Equipment" "BP-1-A0116"
"BNB"   "R2017100026501" "Earth Moving Equipment" "BP-1-A0117"
"BNB"   "R2017060021174" "Earth Moving Equipment" "BP-1-A0118"
"BNB"   "R2019080041470" "Earth Moving Equipment" "BP-1-A0120"
"BNB"   "R2017050020412" "Earth Moving Equipment" "BP-1-A0121"
"BNB"   "R2017050020634" "Earth Moving Equipment" "BP-1-A0123"
"BNB"   "R2019120044831" "Earth Moving Equipment" "BP-1-A0123"
"BIL"   "R2017050020608" "Earth Moving Equipment" "BP-1-A0124"
"BNB"   "R2017070021635" "Earth Moving Equipment" "BP-1-A0125"
"BNB"   "R2017070021322" "Earth Moving Equipment" "BP-1-A0127"
"BNB"   "R2019040039382" "Earth Moving Equipment" "BP-1-A0128"
"BOB"   "R2021060035553" "Earth Moving Equipment" "BP-1-A0129"
"BIL"   "R2017050020610" "Earth Moving Equipment" "BP-1-A0131"
"BNB"   "R2017090022390" "Earth Moving Equipment" "BP-1-A0132"
"BNB"   "R2017050020613" "Earth Moving Equipment" "BP-1-A0133"
"BIL"   "R2017120027435" "Earth Moving Equipment" "BP-1-A0134"
"BIL"   "R2017120027435" "Earth Moving Equipment" "BP-1-A0135"
"BNB"   "R2017070021594" "Earth Moving Equipment" "BP-1-A0137"
"RICBL" "R2017110026947" "Earth Moving Equipment" "BP-1-A0139"
"BOB"   "R2017080021949" "Earth Moving Equipment" "BP-1-A0140"
"BNB"   "R2017060020909" "Earth Moving Equipment" "BP-1-A0142"
"BIL"   "R2020010045305" "Earth Moving Equipment" "BP-1-A0143"
"BNB"   "R2017060020846" "Earth Moving Equipment" "BP-1-A0145"
"BNB"   "R2017070021587" "Earth Moving Equipment" "BP-1-A0146"
"BNB"   "R2019010037879" "Earth Moving Equipment" "BP-1-A0147"
end

Thank you

[Nick Cox]

See FAQ https://www.stata.com/support/faqs/d...ions-in-group/

[andrew roder]

I have looked at it but it was confusing. would appreciate if you can help

Thank you

[andrew roder]

Ans also I have to consider more than one variable unlike in the example

[Nick Cox]

Sorry that you are confused. Two classifying variables rather than one are immaterial here. It is the same principle.

.  bysort Identifier_Type Vehicle_No (RegistrationNo) : gen diff = RegistrationNo[1] != RegistrationNo[_N] 

.  
.  list Identifier_Type Vehicle_No RegistrationNo if diff , sepby(Identifier_Type Vehicle_No) 

     +------------------------------------------------------+
     |        Identifier_Type   Vehicle_No   RegistrationNo |
     |------------------------------------------------------|
 11. | Earth Moving Equipment   BP-1-A0018   R2016100017684 |
 12. | Earth Moving Equipment   BP-1-A0018   R2016100017922 |
     |------------------------------------------------------|
 24. | Earth Moving Equipment   BP-1-A0049   R2017080022035 |
 25. | Earth Moving Equipment   BP-1-A0049   R2018050029405 |
     |------------------------------------------------------|
 42. | Earth Moving Equipment   BP-1-A0079   R2017030019594 |
 43. | Earth Moving Equipment   BP-1-A0079   R2018080031295 |
     |------------------------------------------------------|
 45. | Earth Moving Equipment   BP-1-A0081   R2016120018715 |
 46. | Earth Moving Equipment   BP-1-A0081   R2017020019108 |
     |------------------------------------------------------|
 63. | Earth Moving Equipment   BP-1-A0101   R2017040020299 |
 64. | Earth Moving Equipment   BP-1-A0101   R2020060046843 |
     |------------------------------------------------------|
 70. | Earth Moving Equipment   BP-1-A0112   R2017040020178 |
 71. | Earth Moving Equipment   BP-1-A0112   R2022050041746 |
     |------------------------------------------------------|
 75. | Earth Moving Equipment   BP-1-A0116   R2016060015772 |
 76. | Earth Moving Equipment   BP-1-A0116   R2017050020406 |
     |------------------------------------------------------|
 81. | Earth Moving Equipment   BP-1-A0123   R2017050020634 |
 82. | Earth Moving Equipment   BP-1-A0123   R2019120044831 |
     +------------------------------------------------------+

[andrew roder]

That is fine but what if we also want it by Same agency and different agency. THe same conditions that you have shown above but I want it by Different banks and same banks

[andrew roder]

That is fine but what if we also want it by Same agency and different agency. THe same conditions that you have shown above but I want it by Different banks and same banks

[Nick Cox]

This is also discussed in the FAQ cited in #2.