sorting out data based on count

[Nishan Lamichhane]

Dear all,

please help me sort it out. I want to delete observations if proportion of positive/negative is higher than the other for the same testdate.

For example: if for id 1207307 on 19 oct there are more positive test results than negative then we keep the first positive, similar for negative if there are more negatives we keep the first negative from the same date.
we will have only one observation for each testdate. We will keep those observations when rep1==. (which means no more than 1 tests were done on that day.

If there are equal number of positive test(s) and negative test(s), then we change the rest result (I mean replace) with what the result was on the previous latest date for that id.

if for an id and testdate, if all are postive, we keep the only one positive // and if all negative we keep only one negative for that date.

Remember we work only on repeated measure ( for same date) so we do not delete if rep1==.

Any help will be much appreciated. Thank you in advance

* Example generated by -dataex-. For more info, type help dataex
clear
input double id long testdate byte testresult float(startdate enddate rep countbyindv countsameday num1 prop1 rep1)
1207287 14129 0 13903 14185 0 4 1 0        0 .
1207287 14156 1 13903 14185 0 5 1 1        1 .
1207306 18982 0 18892 19158 0 1 1 0        0 .
1207306 19073 0 18892 19158 0 2 1 0        0 .
1207306 19155 1 18892 19158 0 3 1 1       .5 1
1207306 19155 1 18892 19158 1 4 2 2        1 1
1207306 19158 1 18892 19158 0 5 1 1        1 .
1207307 18332 0 18283 18555 0 1 1 0        0 .
1207307 18450 0 18283 18555 0 2 1 0        0 .
1207307 18554 0 18283 18555 0 3 1 0        0 1
1207307 18554 1 18283 18555 1 4 2 1      .25 1
1207307 18554 1 18283 18555 1 5 3 2       .5 1
1207307 18554 1 18283 18555 1 6 4 3      .75 1
1207337 17540 0 17437 17707 0 1 1 0        0 .
1207337 17549 1 17437 17707 0 2 1 1        1 .
1207337 17658 1 17437 17707 0 3 1 1        1 .
1207337 17668 1 17437 17707 0 4 1 1        1 .
1207337 17695 0 17437 17707 0 5 1 0        0 .
1207337 17700 0 17437 17707 0 6 1 0        0 .
1207346 14822 1 14761 15059 0 1 1 1        1 .
1207346 15012 1 14761 15059 0 2 1 1        1 .
1207410 17273 0 17193 17476 0 1 1 0        0 .
1207410 17365 1 17193 17476 0 2 1 1        1 .
1207410 17444 0 17193 17476 0 3 1 0        0 .
1207524 13775 0 13521 13790 0 1 1 0        0 .
1207524 13789 1 13521 13790 0 2 1 1       .5 1
1207524 13789 1 13521 13790 1 3 2 2        1 1
1207576 12544 0 12369 12635 0 1 1 0        0 1
1207576 12544 1 12369 12635 1 2 2 1       .5 1
1207576 12608 0 12369 12635 0 3 1 0        0 .
1207595 18928 1 18864 19136 0 1 1 1        1 .
1207595 18934 1 18864 19136 0 2 1 1        1 .
1207595 19065 1 18864 19136 0 3 1 1        1 .
1207595 19099 1 18864 19136 0 4 1 1        1 .
1207595 19124 1 18864 19136 0 5 1 1        1 .
1207595 19131 1 18864 19136 0 6 1 1        1 .
1207595 19136 1 18864 19136 0 7 1 1        1 .
1207689 16854 1 16675 16962 0 1 1 1        1 .
1207689 16926 0 16675 16962 0 2 1 0        0 .
1207689 16962 0 16675 16962 0 3 1 0        0 .
1207695 14418 1 14344 14612 0 1 1 1        1 .
1207695 14419 1 14344 14612 0 2 1 1        1 .
1207695 14507 1 14344 14612 0 3 1 1        1 .
1207705 18497 0 18333 18600 0 1 1 0        0 .
1207705 18577 1 18333 18600 0 2 1 1        1 .
1207705 18589 1 18333 18600 0 3 1 1        1 .
1207806 17322 0 17260 17534 0 1 1 0        0 .
1207806 17441 1 17260 17534 0 2 1 1        1 .
1207806 17514 0 17260 17534 0 3 1 0        0 .
1207806 17533 0 17260 17534 0 4 1 0        0 .
1207974 12849 1 12787 13071 0 1 1 1        1 .
1207974 13038 1 12787 13071 0 2 1 1        1 .
1208017 15376 1 15298 15578 0 1 1 1        1 .
1208033 17528 0 17467 17734 0 1 1 0        0 .
1208033 17734 1 17467 17734 0 2 1 1       .5 1
1208033 17734 1 17467 17734 1 3 2 2        1 1
1208058 16590 1 16409 16702 0 1 1 1        1 .
1208058 16656 0 16409 16702 0 2 1 0        0 .
1208069 14677 1 14604 14903 0 1 1 1        1 .
1208138 19087 0 19005 19277 0 1 1 0        0 .
1208138 19179 0 19005 19277 0 2 1 0        0 .
1208138 19277 1 19005 19277 0 3 1 1        1 .
1208222 15260 1 15193 15472 0 1 1 1        1 .
1208222 15440 1 15193 15472 0 2 1 1        1 .
1208223 17665 1 17574 17851 0 1 1 1        1 .
1208223 17729 0 17574 17851 0 2 1 0        0 .
1208223 17833 0 17574 17851 0 3 1 0        0 .
1208282 16516 0 16353 16613 0 1 1 0        0 .
1208282 16613 1 16353 16613 0 2 1 1       .5 1
1208282 16613 1 16353 16613 1 3 2 2        1 1
1208353 18141 1 18058 18334 0 1 1 1        1 .
1208353 18241 1 18058 18334 0 2 1 1        1 .
1208419 19023 0 18932 19198 0 1 1 0        0 .
1208419 19197 1 18932 19198 0 2 1 1 .3333333 1
1208419 19197 1 18932 19198 1 3 2 2 .6666667 1
1208419 19197 1 18932 19198 1 4 3 3        1 1
1208465 15677 1 15502 15784 0 1 1 1        1 .
1208465 15757 0 15502 15784 0 2 1 0        0 .
1208473 16394 0 16320 16588 0 1 1 0        0 .
1208473 16467 1 16320 16588 0 2 1 1        1 .
1208473 16468 1 16320 16588 0 3 1 1        1 .
1208473 16495 1 16320 16588 0 4 1 1        1 .
1208512 13765 1 13589 13861 0 1 1 1        1 .
1208607  9897 1  9821 10093 0 1 1 1        1 .
1208614 14067 0 14018 14304 0 1 1 0        0 .
1208614 14213 1 14018 14304 0 2 1 1        1 .
1208614 14271 0 14018 14304 0 3 1 0        0 .
1208614 14304 0 14018 14304 0 4 1 0        0 .
1208634 13999 0 13910 14187 0 1 1 0        0 .
1208634 14112 1 13910 14187 0 2 1 1        1 .
1208634 14162 0 13910 14187 0 3 1 0        0 .
1208693 16371 0 16293 16575 0 1 1 0        0 .
1208693 16476 0 16293 16575 0 2 1 0        0 .
1208693 16545 0 16293 16575 0 3 1 0        0 .
1208693 16575 1 16293 16575 0 4 1 1        1 .
1208727 18617 0 18563 18858 0 1 1 0        0 .
end
format %tdD_m_Y testdate
format %tdD_m_Y startdate
format %tdD_m_Y enddate
label values testresult posneg
label def posneg 0 "negative", modify
label def posneg 1 "positive", modify

[Clyde Schechter]

The following code comes close to doing what you want:

replace rep1 = 0 if missing(rep1)

by id testdate, sort: egen mean_test_result = mean(testresult)
gen new_result = round(mean_test_result, 1) if rep1 & mean_test_result != 0.5
replace new_result = testresult if !rep1

//  now deal with the case of equal numbers of positives and negatives
gen previous = .
by id (testdate): replace previous = cond(!missing(new_result[_n-1]), ///
    new_result[_n-1], previous[_n-1])
replace new_result = previous if missing(new_result)

The answer you are (almost) looking for is in the variable new_result.

The changing of rep1 to 0 when it is currently coded as missing is necessary to keep the code simple here. It is, in general, bad practice in Stata to code a yes/no variable as 1/.; 1/0 is almost always better. And in this case the 1/0 is much better, because working with it as a 1/. variable would require slightly more complicated code in this case.

The code does not fulfill your final condition "If there are equal number of positive test(s) and negative test(s), then we change the rest result (I mean replace) with what the result was on the previous latest date for that id," because the condition is ill posed. Even in your own data, the one instance where this occurs, with id 1207576, occurs on that id's very first date, so there is no previous value to use here. You also do not state what you want to do if on date n there is a definitive positive or negative value for a given id, but we have equal numbers of positive and negative values on both dates n+1 and n+2. It is clear enough that for date n+1, the result from date n should be carried forward. But it is unclear what to do with date n+2. Do we leave it undefined (i.e. missing), or do we further propagate the result from date n down to n+2. (And similarly if there are even more adjacent dates with equal numbers of positives and negatives.) The code above assumes we propagate the result as long as needed. That part of the code is, however, untested, as there are no examples of this in the data shown. I believe it works as written, but it's a little subtle and I may have gotten it wrong.