Broad time-based de-deduplication

[Robert Shaw]

Dear statlisters,

I think this is more a problem of logic and using basic bysort commands, however I am hitting a block and coding what I perceive to be nonsence.

I have panel data. Each individual gets sampled potentially many times over the course of time. The sample result (date stamped) is one of ~200 categories (if positive) or negative. Sampling is not 100% sensitive and so a negative in close temporal proximity to any positive might be regarded as a false negative. Sampling can occur very frequently, and there may be several samples in a short space of time resulting in 1) repetition of the same category positive, 2) different category positives close to one another, 3) the same as 1 and 2, but mixed with additional negatives, 4) only negatives.

Objective: To deduplicate the data by defining an 'episode' defined by duration of time and remove 'duplicates'.

Aim 1
Per individual: Remove negatives which are sufficiently close to a positive as to regard them as duplicate measures (which were insufficiently sensitive). eg If (abs(Date[positive] - Date[negative]))<14 then drop Negative (ie within 14 days of a positive - it doesn't matter if there are positives closer to that particular negative, it will get dropped regardless)

Aim 2
Following on from Aim 1. Now classifying positives as per their subgroup eg A, B, C. eg Per individual positive-A episode: Deduplicate by dropping all positive-As within 14 days of the first positive-A, then drop all positive A's within 14 days of the next 'not-yet-dropped' positive-A, until reaching the end of that individual

There is a further problem down the line of how to classify episodes which have more than one category of positive in them, but I need to think about this further.

Thanks for your thoughts.
Kind regards
Robert

[Robert Shaw]

By posting a question on here, I have instantly had some clarity.
Aim 1:

gen todrop=0

forvalues i=1/189 {
    by ID Positive (collectiondate), sort: gen firstpos=1 if _n==`i' & Positive==1
    gen firstposcollectiondate=collectiondate if firstpos==1
    bysort ID: ereplace firstposcollectiondate=min(firstposcollectiondate)
    gen diffdate=collectiondate-firstposcollectiondate
    replace todrop=1 if Positive==0 & diffdate<14 & diffdate>-14
    drop diffdate firstposcollectiondate firstpos
}

[Robert Shaw]

.

[Hemanshu Kumar]

Please provide a data example using dataex, a suggested in the Statalist FAQ #12.

[Robert Shaw]

Dear Hemanshu,
Thank you for the suggestion. In the end, this was a slightly pointless post and a waste of space on the server. The problem lent itself to systematic looping with creation of two new variables. It's amazing how externalising the problem gives clarity of thought. Thank you for taking the time to read

Aim #2

levelsof categorypositive, local(levels)
foreach j of local levels {
    bysort clusterid categorypositive: gen N=_N if categorypositive==`j'
    summarize N 
    forvalues i=1/`r(max)' {
        by ID categorypositive(collectiondate), sort: gen first=1 if _n==`i' & categorypositive==`j'
        gen firstcollectiondate=collectiondate if first==1
        bysort ID: ereplace firstcollectiondate=min(firstcollectiondate)
        gen diffdate=collectiondate-firstcollectiondate
        replace todrop=1 if diffdate<14 & diffdate>0 & categorypositive==`j'
        drop diffdate firstcollectiondate first
    }
    drop N
}

drop if todrop==1
drop todrop

[Hemanshu Kumar]

I'm glad you figured it out! And thank you for posting the solution -- it will be helpful to others in the future!

[Nick Cox]

Anyone attracted by the thread title might find panelthin from SSC of use or interest. The problem here is more complicated.