Hi,
I have a panel dataset, with each person being repeated for mon-sat, depending on how many times they logged in. So its not a balanced panel, i.e., a person could have logged in 4 times on Mon but 7 times on Wed. Each day, they can enter a ticket nos, but can also leave the field blank. Thus I have a var called ticket , which includes those ticket nos. Based on the length, some of those are valid while some are not, and I have a dummy called valid indicating that. I need to draw one random ticket number for each ID, for each day, from the set of valid ticket IDs (var called total_valid_day), but without duplicates.
This is part of a verification purpose, thus we would like to follow up on unique tickets on each day. For example, ID 330 logged in only on Monday, that too once and did not provide any ticket nos, so there is nothing we can follow up on. But ID 336 logged in every day, and sometimes re-used their ticket nos across days.
I tried the following. I stacked the invalid tickets at the bottom. For the first obs of each ID-day, I gen a random integer. And then pick up the ticket nos against that random integer. But I am still left with duplicates, cause my code is not addressing that currently, and I don't know how too.
Any help is appreciated. Let me know if I can provide any clarifications.
I have a panel dataset, with each person being repeated for mon-sat, depending on how many times they logged in. So its not a balanced panel, i.e., a person could have logged in 4 times on Mon but 7 times on Wed. Each day, they can enter a ticket nos, but can also leave the field blank. Thus I have a var called ticket , which includes those ticket nos. Based on the length, some of those are valid while some are not, and I have a dummy called valid indicating that. I need to draw one random ticket number for each ID, for each day, from the set of valid ticket IDs (var called total_valid_day), but without duplicates.
This is part of a verification purpose, thus we would like to follow up on unique tickets on each day. For example, ID 330 logged in only on Monday, that too once and did not provide any ticket nos, so there is nothing we can follow up on. But ID 336 logged in every day, and sometimes re-used their ticket nos across days.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int id str13 ticket float(day_report valid total_valid_day sent_ticket) 329 "3468150857" 1 1 4 1 329 "346815057" 1 0 4 1 329 "346815057" 1 0 4 1 329 "3463186661" 1 1 4 1 329 "3463186661" 1 1 4 1 329 "3464609919" 1 1 4 1 329 "3459325340" 2 1 5 1 329 "3408416699" 2 1 5 1 329 "3472111451" 2 1 5 1 329 "3491525378" 2 1 5 1 329 "3491525378" 2 1 5 1 329 "3028050354" 3 1 5 1 329 "3028050354" 3 1 5 1 329 "3442262306" 3 1 5 1 329 "3442262306" 3 1 5 1 329 "3442262306" 3 1 5 1 329 "3452790953" 4 1 6 1 329 "3429626929" 4 1 6 1 329 "3464613163" 4 1 6 1 329 "3437325520" 4 1 6 1 329 "3439683811" 4 1 6 1 329 "3464613163" 4 1 6 1 329 "3449622374" 5 1 5 1 329 "4496527728" 5 1 5 1 329 "3442527728" 5 1 5 1 329 "3469551129" 5 1 5 1 329 "3489008262" 5 1 5 1 329 "3402846833" 6 1 5 1 329 "3479419239" 6 1 5 1 329 "3479419239" 6 1 5 1 329 "3429128151" 6 1 5 1 329 "3429128151" 6 1 5 1 330 "" 1 . 0 0 332 "3479313088" 1 1 6 1 332 "3499877055" 1 1 6 1 332 "3438971303" 1 1 6 1 332 "3449622370" 1 1 6 1 332 "3479237180" 1 1 6 1 332 "3449905426" 1 1 6 1 332 "3463594054" 2 1 5 1 332 "3439264630" 2 1 5 1 332 "3435989935" 2 1 5 1 332 "3439264664" 2 1 5 1 332 "3479120210" 2 1 5 1 332 "3429233209" 3 1 6 1 332 "3415466217" 3 1 6 1 332 "3442103770" 3 1 6 1 332 "3439141873" 3 1 6 1 332 "3479120210" 3 1 6 1 332 "3415429212" 3 1 6 1 332 "3191985314" 4 1 5 1 332 "3415081651" 4 1 5 1 332 "3415081651" 4 1 5 1 332 "3469312012" 4 1 5 1 332 "3469312012" 4 1 5 1 332 "3469312012" 5 1 5 1 332 "3452828606" 5 1 5 1 332 "3413946655" 5 1 5 1 332 "3405016569" 5 1 5 1 332 "3485492315" 5 1 5 1 332 "3450597651" 6 1 6 1 332 "3429628178" 6 1 6 1 332 "3429628178" 6 1 6 1 332 "3479883617" 6 1 6 1 332 "3479313096" 6 1 6 1 332 "3439625388" 6 1 6 1 333 "3419033622" 1 1 6 1 333 "3469432072" 1 1 6 1 333 "3469432346" 1 1 6 1 333 "3459451417" 1 1 6 1 333 "3468335241" 1 1 6 1 333 "3431953835" 1 1 6 1 333 "3429669677" 2 1 7 1 333 "3449638691" 2 1 7 1 333 "3432049197" 2 1 7 1 333 "3456426493" 2 1 7 1 333 "3494315789" 2 1 7 1 333 "3499995856" 2 1 7 1 333 "3405435155" 2 1 7 1 333 "3409114563" 3 1 6 1 333 "3445898853" 3 1 6 1 333 "3455303031" 3 1 6 1 333 "3488450181" 3 1 6 1 333 "3439399017" 3 1 6 1 333 "3488450181" 3 1 6 1 333 "3445898853" 4 1 7 1 333 "3485333967" 4 1 7 1 333 "3499877040" 4 1 7 1 333 "3441581976" 4 1 7 1 333 "3488796674" 4 1 7 1 333 "3474442832" 4 1 7 1 333 "3417330160" 4 1 7 1 333 "3464647584" 5 1 5 1 333 "3439732778" 5 1 5 1 333 "3429785219" 5 1 5 1 333 "3405822358" 5 1 5 1 333 "3409689369" 5 1 5 1 333 "3412181572" 6 1 5 1 333 "3401128811" 6 1 5 1 333 "3439403595" 6 1 5 1 333 "3499091917" 6 1 5 1 333 "3428931443" 6 1 5 1 334 "3449886010" 1 1 6 1 334 "3449886562" 1 1 6 1 334 "3449886010" 1 1 6 1 334 "3470890745" 1 1 6 1 334 "3441895041" 1 1 6 1 334 "3441895041" 1 1 6 1 334 "3429757849" 2 1 6 1 334 "3499091998" 2 1 6 1 334 "3449641860" 2 1 6 1 334 "3479696922" 2 1 6 1 334 "3469683646" 2 1 6 1 334 "3449886010" 2 1 6 1 334 "3419422300" 3 1 6 1 334 "3420989969" 3 1 6 1 334 "3432256652" 3 1 6 1 334 "3479695664" 3 1 6 1 334 "3490300070" 3 1 6 1 334 "3490300070" 3 1 6 1 334 "3459103337" 4 1 5 1 334 "345597770" 4 0 5 1 334 "3470890745" 4 1 5 1 334 "3426228598" 4 1 5 1 334 "3429534622" 4 1 5 1 334 "3460523583" 4 1 5 1 334 "3439620885" 5 1 9 1 334 "3459142928" 5 1 9 1 334 "" 5 . 9 0 334 "3468151295" 5 1 9 1 334 "3458927322" 5 1 9 1 334 "3404880338" 5 1 9 1 334 "3449432076" 5 1 9 1 334 "3449906357" 5 1 9 1 334 "3449906357" 5 1 9 1 334 "3428274741" 5 1 9 1 336 "3449679838" 1 1 1 1 336 "449679838" 1 0 1 1 336 "345940662" 2 0 5 1 336 "3459405662" 2 1 5 1 336 "3444991637" 2 1 5 1 336 "3444991637" 2 1 5 1 336 "3444991637" 2 1 5 1 336 "3453683212" 2 1 5 1 336 "3453683212" 3 1 6 1 336 "3453683212" 3 1 6 1 336 "3453683212" 3 1 6 1 336 "3453683212" 3 1 6 1 336 "3453683212" 3 1 6 1 336 "3453683212" 3 1 6 1 336 "3486549657" 4 1 6 1 336 "3453683212" 4 1 6 1 336 "3453683212" 4 1 6 1 336 "3459405662" 4 1 6 1 336 "3444991637" 4 1 6 1 336 "3453683212" 4 1 6 1 336 "3438974573" 5 1 6 1 336 "3438974573" 5 1 6 1 336 "3438974573" 5 1 6 1 336 "3449679838" 5 1 6 1 336 "3449679838" 5 1 6 1 336 "3449679838" 5 1 6 1 336 "3438974573" 6 1 6 1 336 "3438974573" 6 1 6 1 336 "3449679838" 6 1 6 1 336 "3449679838" 6 1 6 1 336 "3438974573" 6 1 6 1 336 "3449679838" 6 1 6 1 339 "341025223" 1 0 5 1 339 "3463443112" 1 1 5 1 339 "3463443112" 1 1 5 1 339 "3439566114" 1 1 5 1 339 "3439566114" 1 1 5 1 339 "3473839506" 1 1 5 1 339 "" 1 . 5 0 339 "3483328910" 2 1 5 1 339 "3483328910" 2 1 5 1 339 "3412279800" 2 1 5 1 339 "3412279800" 2 1 5 1 339 "3439584282" 2 1 5 1 339 "3464122355" 3 1 3 1 339 "3439566114" 3 1 3 1 339 "3459097018" 3 1 3 1 339 "3459097018" 4 1 4 1 339 "3459097018" 4 1 4 1 339 "3459097018" 4 1 4 1 339 "3459097018" 4 1 4 1 339 "" 4 . 4 0 339 "3497835086" 5 1 5 1 339 "3497835086" 5 1 5 1 339 "3459097018" 5 1 5 1 339 "3467716917" 5 1 5 1 339 "3467716917" 5 1 5 1 339 "3467716917" 6 1 6 1 339 "3467716917" 6 1 6 1 339 "3497835086" 6 1 6 1 339 "3449623384" 6 1 6 1 339 "3451521930" 6 1 6 1 339 "3451521930" 6 1 6 1 986 "3443593943" 1 1 6 1 986 "3480961418" 1 1 6 1 986 "3450454500" 1 1 6 1 986 "3485409747" 1 1 6 1 986 "3466644565" 1 1 6 1 986 "3466644565" 1 1 6 1 986 "3465592751" 2 1 5 1 986 "3454204210" 2 1 5 1 986 "3469503378" 2 1 5 1 986 "3462928300" 2 1 5 1 986 "3449623835" 2 1 5 1 986 "3499272276" 3 1 5 1 986 "3428914684" 3 1 5 1 986 "3439635676" 3 1 5 1 986 "3429016686" 3 1 5 1 986 "3445064093" 3 1 5 1 986 "34624093777" 3 . 5 1 986 "3429621472" 4 1 6 1 986 "3450937502" 4 1 6 1 986 "3457707706" 4 1 6 1 986 "3429060382" 4 1 6 1 986 "3433313904" 4 1 6 1 986 "3469855619" 4 1 6 1 986 "3463410942" 5 1 4 1 986 "3479100963" 5 1 4 1 986 "3443537650" 5 1 4 1 986 "3439520878" 5 1 4 1 986 "3469994299" 6 1 5 1 986 "3449678822" 6 1 5 1 986 "3429603334" 6 1 5 1 986 "3452235115" 6 1 5 1 986 "3452920946" 6 1 5 1 987 "3471907442" 1 1 6 1 987 "3159658600" 1 1 6 1 987 "3408822897" 1 1 6 1 987 "3408822897" 1 1 6 1 987 "3419661800" 1 1 6 1 987 "3419661800" 1 1 6 1 987 "3461201096" 2 1 6 1 987 "3439833941" 2 1 6 1 987 "3475196820" 2 1 6 1 987 "3472305586" 2 1 6 1 987 "3412742041" 2 1 6 1 987 "3348210639" 2 1 6 1 987 "3416146810" 3 1 5 1 987 "348699802" 3 0 5 1 987 "3450774588" 3 1 5 1 987 "3450774588" 3 1 5 1 987 "3449772327" 3 1 5 1 987 "3499433221" 3 1 5 1 987 "3499433221" 4 1 6 1 987 "3139412272" 4 1 6 1 987 "3459277018" 4 1 6 1 987 "3468984697" 4 1 6 1 987 "3439602591" 4 1 6 1 987 "3470898136" 4 1 6 1 987 "3409839174" 5 1 6 1 987 "3495942720" 5 1 6 1 987 "3409490729" 5 1 6 1 987 "3438994715" 5 1 6 1 987 "3449683637" 5 1 6 1 987 "3439392263" 5 1 6 1 987 "3449629661" 6 1 6 1 987 "3449629661" 6 1 6 1 987 "3454079462" 6 1 6 1 987 "3440091789" 6 1 6 1 987 "3479682149" 6 1 6 1 987 "3444243401" 6 1 6 1 993 "3449636951" 1 1 4 1 993 "3478978886" 1 1 4 1 993 "3443725314" 1 1 4 1 993 "" 1 . 4 0 993 "" 1 . 4 0 993 "3420901575" 1 1 4 1 993 "" 1 . 4 0 993 "" 2 . 5 0 993 "3349356208" 2 1 5 1 993 "3459822888" 2 1 5 1 993 "" 2 . 5 0 993 "3469415314" 2 1 5 1 993 "3469415315" 2 1 5 1 993 "3439743948" 2 1 5 1 993 "3479064133" 3 1 5 1 993 "3409005509" 3 1 5 1 993 "" 3 . 5 0 993 "3409005509" 3 1 5 1 993 "" 3 . 5 0 993 "3490941114" 3 1 5 1 993 "3441201542" 3 1 5 1 993 "3465793097" 4 1 5 1 993 "3476026486" 4 1 5 1 993 "3463137810" 4 1 5 1 993 "" 4 . 5 0 993 "3449636951" 4 1 5 1 993 "3478978886" 4 1 5 1 993 "" 4 . 5 0 993 "3428166887" 5 1 7 1 993 "3480725481" 5 1 7 1 993 "3438977516" 5 1 7 1 993 "3420901575" 5 1 7 1 993 "3474164061" 5 1 7 1 993 "3429663227" 5 1 7 1 993 "3443725314" 5 1 7 1 993 "340915" 6 . 5 1 993 "3409715133" 6 1 5 1 993 "3409715133" 6 1 5 1 993 "3429547855" 6 1 5 1 993 "3488979230" 6 1 5 1 993 "3466005015" 6 1 5 1 997 "3492449396" 1 1 5 1 997 "3449664139" 1 1 5 1 997 "3468848203" 1 1 5 1 997 "3445063751" 1 1 5 1 997 "3489062865" 1 1 5 1 997 "439384373" 1 0 5 1 997 "3449064207" 2 1 6 1 997 "3479063785" 2 1 6 1 997 "3449800062" 2 1 6 1 997 "3448136823" 2 1 6 1 997 "3471285148" 2 1 6 1 997 "3495850635" 2 1 6 1 997 "3453226445" 3 1 5 1 997 "3459757185" 3 1 5 1 997 "3409134521" 3 1 5 1 997 "3409135320" 3 1 5 1 997 "3489062786" 3 1 5 1 997 "3449680682" 4 1 5 1 997 "3447445646" 4 1 5 1 997 "3465670110" 4 1 5 1 997 "3464618760" 4 1 5 1 997 "3430036730" 4 1 5 1 997 "3439067625" 5 1 4 1 997 "3449206620" 5 1 4 1 997 "3419526378" 5 1 4 1 997 "3451946410" 5 1 4 1 997 "3409134521" 6 1 5 1 997 "3479123282" 6 1 5 1 997 "3496508893" 6 1 5 1 997 "3442840085" 6 1 5 1 997 "3455526165" 6 1 5 1 1001 "3449623394" 1 1 6 1 1001 "3449679079" 1 1 6 1 1001 "3479675420" 1 1 6 1 1001 "3429864229" 1 1 6 1 1001 "3452244544" 1 1 6 1 1001 "3468157006" 1 1 6 1 1001 "3449780775" 2 1 6 1 1001 "3449623403" 2 1 6 1 1001 "3432772901" 2 1 6 1 1001 "3409598881" 2 1 6 1 1001 "3449073081" 2 1 6 1 1001 "3443372052" 2 1 6 1 1001 "3411237395" 3 1 6 1 1001 "3429812192" 3 1 6 1 1001 "3449671197" 3 1 6 1 1001 "3153367810" 3 1 6 1 1001 "3448122345" 3 1 6 1 1001 "3449890122" 3 1 6 1 1001 "347893771" 4 0 4 1 1001 "3438994679" 4 1 4 1 1001 "3453255504" 4 1 4 1 1001 "3488906693" 4 1 4 1 1001 "3038049804" 4 1 4 1 1001 "3329491512" 5 1 5 1 1001 "3442461211" 5 1 5 1 1001 "3453945893" 5 1 5 1 1001 "3449880633" 5 1 5 1 1001 "3459445700" 5 1 5 1 1001 "3455451926" 6 1 5 1 1001 "3469452992" 6 1 5 1 1001 "3442252458" 6 1 5 1 1001 "3453592200" 6 1 5 1 1001 "3439598236" 6 1 5 1 1002 "3449667020" 1 1 4 1 1002 "3428992255" 1 1 4 1 1002 "3428266696" 1 1 4 1 1002 "" 1 . 4 0 1002 "3439997868" 1 1 4 1 1002 "3449890641" 2 1 2 1 1002 "3439268411" 2 1 2 1 1002 "" 2 . 2 0 1002 "" 2 . 2 0 1002 "" 2 . 2 0 1002 "" 2 . 2 0 1002 "3439236780" 3 1 1 1 1002 "" 3 . 1 0 1002 "" 3 . 1 0 1002 "" 3 . 1 0 1002 "" 3 . 1 0 1002 "" 3 . 1 0 1002 "" 4 . 2 0 1002 "" 4 . 2 0 1002 "3458336280" 4 1 2 1 1002 "" 4 . 2 0 1002 "3429604344" 4 1 2 1 1002 "" 4 . 2 0 1002 "3469452026" 5 1 5 1 1002 "3469452026" 5 1 5 1 1002 "3469452026" 5 1 5 1 1002 "3469430590" 5 1 5 1 1002 "3469452026" 5 1 5 1 1002 "3469415092" 6 1 5 1 1002 "3469415092" 6 1 5 1 1002 "3469415092" 6 1 5 1 1002 "3469415092" 6 1 5 1 1002 "3469415092" 6 1 5 1 1004 "3453029060" 1 1 6 1 1004 "3431946807" 1 1 6 1 1004 "3494402050" 1 1 6 1 1004 "3494402051" 1 1 6 1 1004 "3441997880" 1 1 6 1 1004 "3421651361" 1 1 6 1 1004 "3441997880" 2 1 7 1 1004 "3463060049" 2 1 7 1 1004 "" 2 . 7 0 1004 "3456720776" 2 1 7 1 1004 "3463060049" 2 1 7 1 1004 "3449992216" 2 1 7 1 1004 "3478933776" 2 1 7 1 1004 "3409079997" 2 1 7 1 1004 "3409895682" 3 1 5 1 1004 "3463870067" 3 1 5 1 1004 "34331946807" 3 . 5 1 1004 "3449992216" 3 1 5 1 1004 "3456720776" 3 1 5 1 1004 "3409079997" 3 1 5 1 1004 "3479530544" 4 1 6 1 1004 "3478119625" 4 1 6 1 1004 "3449761600" 4 1 6 1 1004 "3418350817" 4 1 6 1 1004 "3439458009" 4 1 6 1 1004 "3479021166" 4 1 6 1 1004 "3470967645" 5 1 6 1 1004 "3490842238" 5 1 6 1 1004 "3440089618" 5 1 6 1 1004 "3459511197" 5 1 6 1 1004 "3479021166" 5 1 6 1 1004 "3449815593" 5 1 6 1 1004 "3472040852" 6 1 6 1 1004 "3474435404" 6 1 6 1 1004 "3474435404" 6 1 6 1 1004 "3465652067" 6 1 6 1 1004 "3447806820" 6 1 6 1 1004 "3495943386" 6 1 6 1 1005 "3408811330" 1 1 6 1 1005 "3449898053" 1 1 6 1 1005 "3434104658" 1 1 6 1 1005 "3449880661" 1 1 6 1 1005 "" 1 . 6 0 1005 "3419823058" 1 1 6 1 1005 "3419823058" 1 1 6 1 1005 "3449618506" 2 1 4 1 1005 "3433821971" 2 1 4 1 1005 "" 2 . 4 0 1005 "3439603513" 2 1 4 1 1005 "3449880398" 2 1 4 1 1005 "3439602350" 3 1 4 1 1005 "3439390025" 3 1 4 1 1005 "" 3 . 4 0 1005 "3429581077" 3 1 4 1 1005 "3429581077" 3 1 4 1 1005 "3449880398" 4 1 4 1 1005 "3499021498" 4 1 4 1 1005 "3449880398" 4 1 4 1 1005 "" 4 . 4 0 1005 "3239917316" 4 1 4 1 1005 "3465592939" 5 1 6 1 1005 "3025742041" 5 1 6 1 1005 "3459494488" 5 1 6 1 1005 "3163524544" 5 1 6 1 1005 "3163524544" 5 1 6 1 1005 "3418350837" 5 1 6 1 1005 "" 6 . 4 0 1005 "3408811330" 6 1 4 1 1005 "3418350837" 6 1 4 1 1005 "" 6 . 4 0 1005 "3429667128" 6 1 4 1 1005 "3455437803" 6 1 4 1 1014 "3448653231" 1 1 5 1 1014 "3409463452" 1 1 5 1 1014 "3439602394" 1 1 5 1 1014 "3448094752" 1 1 5 1 1014 "" 1 . 5 0 1014 "3405757046" 1 1 5 1 1014 "3434375613" 2 1 5 1 1014 "3439393081" 2 1 5 1 1014 "3449660706" 2 1 5 1 1014 "3411907082" 2 1 5 1 1014 "3449656255" 2 1 5 1 1014 "3439293007" 3 1 5 1 1014 "34534744358" 3 . 5 1 1014 "3489590680" 3 1 5 1 1014 "3469439922" 3 1 5 1 1014 "3325401528" 3 1 5 1 1014 "3469408474" 3 1 5 1 1014 "3469408474" 4 1 5 1 1014 "3471952650" 4 1 5 1 1014 "34496936190" 4 . 5 1 1014 "3469411085" 4 1 5 1 1014 "3403250837" 4 1 5 1 1014 "3454263944" 4 1 5 1 1014 "3439602394" 5 1 6 1 1014 "345913981" 5 0 6 1 1014 "3491569206" 5 1 6 1 1014 "3443862934" 5 1 6 1 1014 "3459451185" 5 1 6 1 1014 "3469383149" 5 1 6 1 1014 "3449800052" 5 1 6 1 1014 "3469867061" 6 1 6 1 1014 "3409801744" 6 1 6 1 1014 "3425519929" 6 1 6 1 end label values day_report days label def days 1 "Mon", modify label def days 2 "Tues", modify label def days 3 "Wed", modify label def days 4 "Thurs", modify label def days 5 "Fri", modify label def days 6 "Sat", modify
I tried the following. I stacked the invalid tickets at the bottom. For the first obs of each ID-day, I gen a random integer. And then pick up the ticket nos against that random integer. But I am still left with duplicates, cause my code is not addressing that currently, and I don't know how too.
Code:
bys id day_report : gen countnonmissing = sum(!missing(ticket)) if !missing(ticket) & sent_ticket != . & valid == 1 bysort id day_report (countnonmissing): gen inter = _n if sent_ticket != . // with invalid phno visits stacked at bottom /*choose integers U[a,b]= U[1,total_phno] *If you want to generate integer random numbers between a and b, use generate ui = floor((b–a+1)*runiform() + a) */ set seed 1234 bys id day_report (countnonmissing) : gen rand = floor((total_valid_day-1+1)*runiform() + 1) if valid == 1 & inter==1 replace rand=rand[_n-1] if day_report==day_report[_n-1] sort id day_report inter tab rand if sent_ticket != .,m // still 2 missing cant be checked up on at all gen ticket_verify = "." bysort id day_report : replace ticket_verify = ticket_verify[_n] if rand==inter duplicates tag id ticket_verify if ticket_verify != "." & sent_ticket!= ., gen(dupl)
Comment