How to identify duplicate serial number and assign a unique code

[Anna Aniston]

Dear All,

I wanted to know how we can identify duplicate serial number within an EA and assign a unique serial number which is not there within that EA. For example the unique code should be the combination of dcode gtcode clcode EA and slno. So for example if in 1 1 1 0 there are three serial number 10 in the example below. I want to replacce them as 10,11 and 12 since these are the numbers missing. For the second EA 1 1 2 0 there are 3 cases with slno 2 i want to replace it with 2, 6 and 7 since these are the missing slno. Below is an example of my part of my dataset. There are about 700 duplicates so I want to know an easier way to generate unique slno. For the EA with no duplicate i want to keep it as it is

Thank you

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

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(dcode gtcode) str3(clcode EA) byte slno
1 1 "1" "0"  1
1 1 "1" "0"  2
1 1 "1" "0"  3
1 1 "1" "0"  4
1 1 "1" "0"  5
1 1 "1" "0"  6
1 1 "1" "0"  7
1 1 "1" "0"  8
1 1 "1" "0"  9
1 1 "1" "0" 10
1 1 "1" "0" 10
1 1 "1" "0" 10
1 1 "2" "0"  1
1 1 "2" "0"  2
1 1 "2" "0"  3
1 1 "2" "0"  4
1 1 "2" "0"  5
1 1 "2" "0"  2
1 1 "2" "0"  2
1 1 "2" "0"  8
1 1 "2" "0"  9
1 1 "2" "0" 10
1 1 "2" "0" 11
1 1 "2" "0" 12
1 1 "3" "0"  1
1 1 "3" "0"  2
1 1 "3" "0"  3
1 1 "3" "0"  3
1 1 "3" "0"  5
1 1 "3" "0"  6
end
label values dcode dcode
label def dcode 1 "Bumthang", modify

[Clyde Schechter]

I'm not entirely sure I understand what you want, but it sounds like it might be:

by dcode gtcode clcode EA (slno), sort: replace slno = _n

[Anna Aniston]

Dear Clyde

Yours is ok but what I wanted was not to replacce the EA with duplicates with running unique serial number but with the missing number, For example

1	2	5	0	1
1	2	5	0	2
1	2	5	0	3
1	2	5	0	4
1	2	5	0	5
1	2	5	0	7
1	2	5	0	8
1	2	5	0	9
1	2	5	0	10
1	2	5	0	11
1	2	5	0	12
1	2	5	0	12

I want to replace the last serial number with the missing serial number ie 6 in the above example. With your command I do get unique numbers but its changing the serial number of the others which are not duplicate as well.

[Ali Atia]

Perhaps:

duplicates tag dcode gtcode clcode EA slno,gen(dups)
by dcode gtcode clcode EA (slno), sort: replace slno = _n if dups>0

[Andrew Musau]

What is wanted is somewhat more demanding as the OP wants to maintain the original slno values within groups. Whether this is crucial, I do not know. The following uses fillin to identify missing slno values within a group and supposes that at least all possible values of slno exist within the dataset. If not, some minor adjustments are needed.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(dcode gtcode) str3(clcode EA) byte slno
1 1 "1" "0"  1
1 1 "1" "0"  2
1 1 "1" "0"  3
1 1 "1" "0"  4
1 1 "1" "0"  5
1 1 "1" "0"  6
1 1 "1" "0"  7
1 1 "1" "0"  8
1 1 "1" "0"  9
1 1 "1" "0" 10
1 1 "1" "0" 10
1 1 "1" "0" 10
1 1 "2" "0"  1
1 1 "2" "0"  2
1 1 "2" "0"  3
1 1 "2" "0"  4
1 1 "2" "0"  5
1 1 "2" "0"  2
1 1 "2" "0"  2
1 1 "2" "0"  8
1 1 "2" "0"  9
1 1 "2" "0" 10
1 1 "2" "0" 11
1 1 "2" "0" 12
1 1 "3" "0"  1
1 1 "3" "0"  2
1 1 "3" "0"  3
1 1 "3" "0"  3
1 1 "3" "0"  5
1 1 "3" "0"  6
end
label values dcode dcode
label def dcode 1 "Bumthang", modify
preserve
contract clcode slno
fillin clcode slno
keep if _fillin
bys clcode(slno): gen order=_n
rename slno slno2
keep clcode sino2 order
tempfile tomerge
save `tomerge'
restore
bys clcode slno: gen dups=_N>1 & _n>1
bys dups clcode (slno): gen order= sum(dups) if dups
replace order=-1000-_n if missing(order)
merge 1:1 clcode order using `tomerge', keep(master match) nogen
gen wanted= cond(!missing(slno2), slno2, slno)

Res.:

. sort clcode slno wanted

. l dcode gtcode clcode EA slno wanted, sepby(clcode)

     +-------------------------------------------------+
     |    dcode   gtcode   clcode   EA   slno   wanted |
     |-------------------------------------------------|
  1. | Bumthang        1        1    0      1        1 |
  2. | Bumthang        1        1    0      2        2 |
  3. | Bumthang        1        1    0      3        3 |
  4. | Bumthang        1        1    0      4        4 |
  5. | Bumthang        1        1    0      5        5 |
  6. | Bumthang        1        1    0      6        6 |
  7. | Bumthang        1        1    0      7        7 |
  8. | Bumthang        1        1    0      8        8 |
  9. | Bumthang        1        1    0      9        9 |
 10. | Bumthang        1        1    0     10       10 |
 11. | Bumthang        1        1    0     10       11 |
 12. | Bumthang        1        1    0     10       12 |
     |-------------------------------------------------|
 13. | Bumthang        1        2    0      1        1 |
 14. | Bumthang        1        2    0      2        2 |
 15. | Bumthang        1        2    0      2        6 |
 16. | Bumthang        1        2    0      2        7 |
 17. | Bumthang        1        2    0      3        3 |
 18. | Bumthang        1        2    0      4        4 |
 19. | Bumthang        1        2    0      5        5 |
 20. | Bumthang        1        2    0      8        8 |
 21. | Bumthang        1        2    0      9        9 |
 22. | Bumthang        1        2    0     10       10 |
 23. | Bumthang        1        2    0     11       11 |
 24. | Bumthang        1        2    0     12       12 |
     |-------------------------------------------------|
 25. | Bumthang        1        3    0      1        1 |
 26. | Bumthang        1        3    0      2        2 |
 27. | Bumthang        1        3    0      3        3 |
 28. | Bumthang        1        3    0      3        4 |
 29. | Bumthang        1        3    0      5        5 |
 30. | Bumthang        1        3    0      6        6 |
     +-------------------------------------------------+

[Clyde Schechter]

Andrew Musau: Brilliant!

[Andrew Musau]

Thanks Clyde Schechter.

[Anna Aniston]

Andrew Musau Thank you so much