Hi. I have a problem with my medical claims data. Claims are identified by a “clmnbr” which can identify a set of different charges to it that occurred on the same day. Dates are identified by “Service_End_Dt” and “Service_Start_Dt”. My goal is to add up the amounts by each claim number. However, as seen in the data sample below, there are negative charges in the dataset for clmnbr 1132068263. The following clmnbr – 1132068264 - has the same number amounts as positive charges, and an additional charge of 96 (last row). Both claim numbers are for charges on the same day How can I identify all such negative and positive pairs across sequential claim numbers? In the example above, after adjusting for all such negative/positive pairs, I should be left with only the charge of 96 for clmnbr 1132068264.
Thank you.


Code:
input str11 clmnbr float(Service_End_Dt Service_Start_Dt) double(clmlnnbr chrgamt)
"1132068263" 21009 21009  1   -17.8
"1132068263" 21009 21009  2  -16.45
"1132068263" 21009 21009  4    -2.2
"1132068263" 21009 21009  6    -231
"1132068263" 21009 21009  9    -137
"1132068263" 21009 21009 11    -117
"1132068263" 21009 21009 12    -111
"1132068263" 21009 21009 15     -59
"1132068263" 21009 21009 17     -45
"1132068263" 21009 21009 18     -42
"1132068263" 21009 21009 19   -1778
"1132068263" 21009 21009 20   -1489
"1132068263" 21009 21009 21     -45
"1132068263" 21009 21009 25 -119.25
"1132068263" 21009 21009 26    -118
"1132068263" 21009 21009 28    -308
"1132068264" 21009 21009  1    17.8
"1132068264" 21009 21009  2   16.45
"1132068264" 21009 21009  4     2.2
"1132068264" 21009 21009  6     231
"1132068264" 21009 21009  9     137
"1132068264" 21009 21009 11     117
"1132068264" 21009 21009 12     111
"1132068264" 21009 21009 15      59
"1132068264" 21009 21009 17      45
"1132068264" 21009 21009 18      42
"1132068264" 21009 21009 19    1778
"1132068264" 21009 21009 20    1489
"1132068264" 21009 21009 25  119.25
"1132068264" 21009 21009 26     118
"1132068264" 21009 21009 28     308
"1132068264" 21009 21009 30      96