Average of previous non-negative observations in panel

[Demetrio Panarello]

Hi everyone,

I have a variable with many negative observations that I need to make positive.
Here's an example:

Var	NeededVar
40	40
10	2
10	2
20	2
-32	2
20	20
15	15
10	1
6	1
-12	2
10	10

When a negative value is found, I need to get a positive average of the negative observation and the smallest number of previous observations.
For instance, -32 is negative; (-32+20) is still negative; (-32+20+10) is still negative; (-32+20+10+10) is positive. So, I would like to replace these observations (-32; 20; 10; 10) by their average (2; 2; 2; 2).

Moreover, I cannot get decimal values: when the average is not an integer, the last observation of the series needs to be adjusted accordingly.
For instance, (-12+6+10) is positive and the sum is 4, to be divided by 3; therefore, I would like to get a series of 1, 1 and 2 (whose sum is still 4) in such a case.

The code I thought about looks like this:

gen Var2 = Var
replace Var2=Var if Var2[_n+1]<0
replace Var=int((Var[_n]+Var[_n+1])/2) if Var2[_n+1]<0
replace Var=(Var2[_n]+Var2[_n-1])-Var[_n-1] if Var2[_n]<0

It works, but only when the sum of the negative observation and the previous one is positive; otherwise, I get two negative values replacing the original ones, making the situation worse.

I may need to create a loop but I did not succeed yet.
I am getting crazy over this issue.
Many thanks for your precious help!
Demetrio

[Demetrio Panarello]

P.S.
I forgot to say it is a panel dataset with about 40K observations. Let's say that all observations in the example are from the same ID and subsequent times.
At worst, it needs about 20 previous observations to get a positive sum.
At the end, the sum of Var and NeededVar should be the same.

[Clyde Schechter]

Interesting problem. I think this does it:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte var
 40
 10
 10
 20
-32
 20
 15
 10
  6
-12
 10
end

gen long obs_no = _n

capture program drop one_obs
program define one_obs
    if ix_var < 0 {
        gsort -obs_no
        gen run_sum = sum(var)
        keep if run_sum <= 0
        gen how_many = _N
        drop run_sum
        keep in 1
    }
    else {
        gen how_many = 0
    }
    exit
end



rangerun one_obs, interval(obs_no . 0) use(var obs_no) sprefix(ix_) verbose
sort obs_no
gen back_to = obs_no - how_many
rangestat (sum) var, interval(obs_no back_to obs_no)
gen average = floor(var_sum/(how_many+1)) if how_many > 0
gen remainder = mod(var_sum, how_many+1) if how_many > 0

gen replacement = .
forvalues i = 1/`=_N' {
    if !missing(average[`i']) {
        local back_to = back_to[`i']
        replace replacement = average[`i']  in `back_to'/`i'
        replace replacement = replacement + remainder in `i'
    }
}
replace replacement = var if missing(replacement)

-rangestat- is by Robert Picard, Nick Cox, and Roberto Ferrer. -rangerun- is by Robert Picard. Both are available from SSC

Note: I don't think this code will do what you want in the event that at some point down the list you have to reach so far back that you go back to a preceding negative value (which would happen, for example if the 10th value of var were -50). Then again, it isn't clear what you might want to do in that circumstance!

Also, if it turns out that you never get to a positive total no matter how far back you go, it is, again, unclear what you might want. This code comes as close as possible, but ...

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Added: Didn't see #2 till after I posted this. The panel structure makes it more complicated. I think that adding the -by(panelvar)- option to the -rangerun- command will do the trick, but I haven't tested that out as there is no panel structure in the example provided.

[Demetrio Panarello]

Dear Clyde,
thank you very much for your quick and valuable answer!
It works perfectly on one of the variables.
Another variable makes some problems. I guess it may be due to the panel structure (unfortunately, rangerun may not be combined with by) or to zero / negative values encountered while going backwards.
For example, ID 4 and times 57/58 work well. At ID 3, times 96 and back, I do not understand what it does.
I attach a -dataex- example of the var and the generated replacement.
Thank you again!

* Example generated by -dataex-. For more info, type help dataex
clear
input int time float(ID var replacement)
  1 1   .   .
  2 1   0   0
  3 1   0   0
  4 1   0   0
  5 1   0   0
  6 1   0   0
  7 1   0   0
  8 1   0   0
  9 1   0   0
 10 1   0   0
 11 1   0   0
 12 1   0   0
 13 1   0   0
 14 1   0   0
 15 1   0   0
 16 1   0   0
 17 1   0   0
 18 1   2   2
 19 1   0   0
 20 1   0   0
 21 1   0   0
 22 1   0   0
 23 1   1   1
 24 1   0   0
 25 1   1   1
 26 1   0   0
 27 1   2   2
 28 1   2   2
 29 1   5   5
 30 1   7   7
 31 1   5   5
 32 1   8   8
 33 1   6   6
 34 1  18  18
 35 1   8   8
 36 1  11  11
 37 1   5   5
 38 1   7   7
 39 1   5   5
 40 1   8   8
 41 1  10  10
 42 1   5   5
 43 1   7   7
 44 1   2   2
 45 1   8   8
 46 1   5   5
 47 1  10  10
 48 1   6   6
 49 1   9   9
 50 1   8   8
 51 1   4   4
 52 1   6   6
 53 1   6   6
 54 1   3   3
 55 1   6   6
 56 1   4   4
 57 1   3   3
 58 1   3   3
 59 1   2   2
 60 1   5   5
 61 1   5   5
 62 1   6   6
 63 1   4   4
 64 1   3   3
 65 1   1   1
 66 1   0   0
 67 1   3   3
 68 1   2   2
 69 1   3   3
 70 1   2   2
 71 1   2   2
 72 1   3   3
 73 1   3   3
 74 1   1   1
 75 1   2   2
 76 1   3   3
 77 1   0   0
 78 1   1   1
 79 1   4   4
 80 1   1   1
 81 1   1   1
 82 1   0   0
 83 1   2   2
 84 1   2   2
 85 1   0   0
 86 1   1   1
 87 1   0   0
 88 1   0   0
 89 1   0   0
 90 1   1   1
 91 1   0   0
 92 1   1   1
 93 1   1   1
 94 1   1   1
 95 1   0   0
 96 1   0   0
 97 1   1   1
 98 1   1   1
 99 1   0   0
100 1   1   1
101 1   0   0
102 1   1   1
103 1   0   0
104 1   0   0
105 1   1   1
106 1   1   1
107 1   0   0
108 1   0   0
109 1   1   1
110 1   0   0
111 1   0   0
112 1   0   0
113 1   1   1
114 1   0   0
115 1   0   0
116 1   0   0
117 1   0   0
118 1   0   0
119 1   0   0
120 1   0   0
121 1   0   0
122 1   0   0
123 1   0   0
124 1   1   1
125 1   0   0
126 1   0   0
127 1   0   0
128 1   1   1
129 1   0   0
130 1   0   0
131 1   0   0
132 1   0   0
133 1   0   0
134 1   0   0
135 1   0   0
136 1   1   1
137 1   0   0
138 1   0   0
139 1   0   0
140 1   0   0
141 1   0   0
142 1   0   0
143 1   0   0
144 1   0   0
145 1   0   0
146 1   0   0
147 1   0   0
148 1   0   0
149 1   0   0
150 1   0   0
151 1   0   0
152 1   0   0
153 1   0   0
154 1   0   0
155 1   0   0
156 1   0   0
157 1   0   0
158 1   0   0
159 1   0   0
160 1   0   0
161 1   0   0
162 1   0   0
163 1   1   1
164 1   0   0
165 1   0   0
166 1   0   0
167 1   0   0
168 1   0   0
169 1   0   0
170 1   0   0
171 1   0   0
172 1   0   0
173 1   0   0
174 1   0   0
175 1   1   1
176 1   1   1
177 1   0   0
178 1   0   0
179 1   0   0
180 1   0   0
181 1   0   0
182 1   0   0
183 1   0   0
184 1   0   0
185 1   0   0
186 1   0   0
187 1   0   0
188 1   0   0
189 1   0   0
190 1   0   0
191 1   1   1
192 1   0   0
193 1   1   1
194 1   0   0
195 1   1   1
196 1   0   0
197 1   0   0
198 1   0   0
199 1   0   0
200 1   0   0
201 1   0   0
202 1   0   0
203 1   1   1
204 1   2   2
205 1   0   0
206 1   3   3
207 1   0   0
208 1   1   1
209 1   0   0
210 1   0   0
211 1   3   3
212 1   1   1
213 1   3   3
214 1   1   1
215 1   2   2
216 1   0   0
217 1   2   2
218 1   1   1
219 1   1   1
220 1   1   1
221 1   1   1
222 1   2   2
223 1   3   3
224 1   2   2
225 1   2   2
226 1   1   1
227 1   4   4
228 1   3   3
229 1   4   4
230 1   2   2
231 1   1   1
232 1   3   3
233 1   2   2
234 1   2   2
235 1   7   7
236 1  10  10
237 1   2   2
238 1   3   3
239 1   3   3
240 1  10  10
241 1  11  11
242 1   8   8
243 1  11  11
244 1   9   9
245 1  11  11
246 1  11  11
247 1  10  10
248 1  10  10
249 1  13  13
250 1  12  12
251 1  18  18
252 1  16  16
253 1  18  18
254 1  14  14
255 1  19  19
256 1  25  25
257 1  34  34
258 1  35  35
259 1  13  13
260 1  27  27
261 1  32  32
262 1  27  27
263 1  40  40
264 1  35  35
265 1  23  23
266 1  36  36
267 1  36  36
268 1  39  39
269 1  44  44
270 1  40  40
271 1  43  43
272 1  43  43
273 1  45  45
274 1  41  41
275 1  48  48
276 1  47  47
277 1  49  49
278 1  47  47
279 1  43  43
280 1  45  45
281 1  49  49
282 1  34  34
283 1  27  27
284 1  34  34
285 1  39  39
286 1  34  34
287 1  36  36
288 1  34  34
289 1  36  36
290 1  34  34
291 1  32  32
292 1  28  28
293 1  23  23
294 1  21  21
295 1  32  32
296 1  31  31
297 1  29  29
298 1  28  28
299 1  22  22
300 1  22  22
301 1  24  24
302 1  26  26
303 1  22  22
304 1  10  10
305 1  26  26
306 1  17  17
307 1  27  27
308 1  15  15
309 1  28  28
310 1  26  26
311 1  29  29
312 1  31  31
313 1  28  28
314 1  28  28
315 1  26  26
316 1  34  34
317 1  36  36
318 1  29  29
319 1  36  36
320 1  35  35
321 1  31  31
322 1  33  33
323 1  37  37
324 1  40  40
325 1  36  36
326 1  36  36
327 1  39  39
328 1  38  38
329 1  35  35
330 1  38  38
331 1  37  37
332 1  37  37
333 1  28  28
334 1  32  32
335 1  33  33
336 1  32  32
337 1  34  34
338 1  36  36
339 1  38  38
340 1  37  37
341 1  37  37
342 1  35  35
343 1  35  35
344 1  30  30
345 1  37  37
346 1  34  34
347 1  24  24
348 1  31  31
349 1  23  23
350 1  25  25
351 1  22  22
352 1  24  24
353 1  29  29
354 1  26  26
355 1  21  21
356 1  20  20
357 1  24  24
358 1  21  21
359 1  22  22
360 1  24  24
361 1  26  26
362 1  22  22
363 1  18  18
364 1  18  18
365 1  19  19
366 1  21  21
367 1  21  21
368 1  15  15
369 1  21  21
370 1  21  21
371 1  21  21
  1 2   .   .
  2 2   0   0
  3 2   0   0
  4 2   0   0
  5 2   0   0
  6 2   0   0
  7 2   0   0
  8 2   0   0
  9 2   0   0
 10 2   0   0
 11 2   2   2
 12 2   2   2
 13 2   1   1
 14 2   0   0
 15 2   8   8
 16 2   4   4
 17 2   4   4
 18 2   5   5
 19 2  20  20
 20 2  13  13
 21 2  22  22
 22 2  30  30
 23 2  22  22
 24 2  21  21
 25 2  21  21
 26 2  34  34
 27 2  29  29
 28 2  45  45
 29 2  32  32
 30 2  59  59
 31 2  75  75
 32 2   0   0
 33 2 120 120
 34 2  48  48
 35 2  67  67
 36 2  65  65
 37 2 105 105
 38 2  32  32
 39 2  97  97
 40 2  60  60
 41 2  85  85
 42 2  40  40
 43 2  83  83
 44 2  68  68
 45 2  59  59
 46 2  76  76
 47 2  78  78
 48 2 101 101
 49 2  96  96
 50 2  97  97
 51 2 101 101
 52 2  88  88
 53 2  79  79
 54 2  77  77
 55 2  81  81
 56 2  79  79
 57 2  78  78
 58 2  76  76
 59 2  74  74
 60 2  71  71
 61 2  69  69
 62 2  68  68
 63 2  56  56
 64 2  55  55
 65 2  58  58
 66 2  67  67
 67 2  63  63
 68 2  31  31
 69 2  29  29
 70 2  26  26
 71 2  34  34
 72 2  30  30
 73 2  31  31
 74 2  35  35
 75 2  23  23
 76 2  26  26
 77 2  36  36
 78 2  33  33
 79 2  28  28
 80 2  32  32
 81 2  33  33
 82 2  64  64
 83 2  37  37
 84 2  18  18
 85 2  20  20
 86 2  47  47
 87 2  39  39
 88 2  24  24
 89 2  15  15
 90 2  14  14
 91 2  12  12
 92 2  15  15
 93 2  14  14
 94 2  16  16
 95 2  10  10
 96 2  13  13
 97 2   7   7
 98 2   9   9
 99 2   9   9
100 2   8   8
101 2  14  14
102 2  12  12
103 2  17  17
104 2   7   7
105 2   7   7
106 2  13  13
107 2   7   7
108 2  15  15
109 2  12  12
110 2   8   8
111 2  10  10
112 2   6   6
113 2   0   0
114 2   6   6
115 2   8   8
116 2   6   6
117 2   7   7
118 2   6   6
119 2   6   6
120 2   4   4
121 2   4   4
122 2   7   7
123 2   5   5
124 2   6   6
125 2   3   3
126 2   5   5
127 2   2   2
128 2   3   3
129 2   1   1
130 2   5   5
131 2   4   4
132 2   2   2
133 2   0   0
134 2   2   2
135 2   3   3
136 2   0   0
137 2   1   1
138 2   2   2
139 2   1   1
140 2   0   0
141 2   1   1
142 2   3   3
143 2   3   3
144 2   0   0
145 2   1   1
146 2   0   0
147 2   0   0
148 2   1   1
149 2   3   3
150 2   0   0
151 2   0   0
152 2   0   0
153 2   2   2
154 2   1   1
155 2   1   1
156 2   0   0
157 2   1   1
158 2   0   0
159 2   1   1
160 2   0   0
161 2   0   0
162 2   2   2
163 2   1   1
164 2   2   2
165 2   1   1
166 2   1   1
167 2   0   0
168 2   0   0
169 2   0   0
170 2   0   0
171 2   2   2
172 2   1   1
173 2   0   0
174 2   0   0
175 2   0   0
176 2   1   1
177 2   1   1
178 2   1   1
179 2   0   0
180 2   0   0
181 2   1   1
182 2   0   0
183 2   0   0
184 2   0   0
185 2   1   1
186 2   0   0
187 2   1   1
188 2   0   0
189 2   1   1
190 2   0   0
191 2   0   0
192 2   1   1
193 2   1   1
194 2   1   1
195 2   1   1
196 2   0   0
197 2   1   1
198 2   1   1
199 2   0   0
200 2   1   1
201 2   0   0
202 2   0   0
203 2   0   0
204 2   0   0
205 2   0   0
206 2   0   0
207 2   0   0
208 2   0   0
209 2   1   1
210 2   0   0
211 2   1   1
212 2   1   1
213 2   1   1
214 2   1   1
215 2   1   1
216 2   1   1
217 2   1   1
218 2   0   0
219 2   2   2
220 2   1   1
221 2   1   1
222 2   1   1
223 2   0   0
224 2   1   1
225 2   1   1
226 2   2   2
227 2   2   2
228 2   0   0
229 2   0   0
230 2   2   2
231 2   3   3
232 2   3   3
233 2   3   3
234 2   4   4
235 2   3   3
236 2   4   4
237 2   2   2
238 2   2   2
239 2   5   5
240 2   6   6
241 2   7   7
242 2  11  11
243 2   9   9
244 2  12  12
245 2  11  11
246 2  14  14
247 2  13  13
248 2  19  19
249 2  27  27
250 2  23  23
251 2  28  28
252 2  11  11
253 2  21  21
254 2  29  29
255 2  37  37
256 2  39  39
257 2  29  29
258 2  45  45
259 2  35  35
260 2  49  49
261 2  64  64
262 2  84  84
263 2  77  77
264 2  61  61
265 2  37  37
266 2  45  45
267 2  71  71
268 2  73  73
269 2  63  63
270 2  78  78
271 2  88  88
272 2  77  77
273 2  69  69
274 2  81  81
275 2  73  73
276 2  84  84
277 2  72  72
278 2  97  97
279 2  98  98
280 2  84  84
281 2  85  85
282 2  64  64
283 2  57  57
284 2  92  92
285 2  54  54
286 2  62  62
287 2  55  55
288 2  64  64
289 2  59  59
290 2  67  67
291 2  84  84
292 2  96  96
293 2  59  59
294 2  36  36
295 2  48  48
296 2  77  77
297 2  89  89
298 2  80  80
299 2  79  79
300 2  38  38
301 2  29  29
302 2  43  43
303 2  50  50
304 2  58  58
305 2  48  48
306 2  28  28
307 2  12  12
308 2  17  17
309 2  20  20
310 2  56  56
311 2  33  33
312 2  29  29
313 2  38  38
314 2  11  11
315 2   8   8
316 2   9   9
317 2  55  55
318 2  45  45
319 2  18  18
320 2  27  27
321 2  32  32
322 2  11  11
323 2  19  19
324 2  42  42
325 2  41  41
326 2  21  21
327 2  20  20
328 2  62  62
329 2  19  19
330 2  19  19
331 2  34  34
332 2  43  43
333 2  29  29
334 2  23  23
335 2  26  26
336 2   9   9
337 2  21  21
338 2  38  38
339 2  51  51
340 2  43  43
341 2  38  38
342 2  59  59
343 2  14  14
344 2  11  11
345 2  37  37
346 2  25  25
347 2  31  31
348 2  22  22
349 2  21  21
350 2   8   8
351 2  12  12
352 2  35  35
353 2  18  18
354 2  22  22
355 2  20  20
356 2  16  16
357 2   6   6
358 2  14  14
359 2  37  37
360 2  22  22
361 2  16  16
362 2  25  25
363 2  12  12
364 2   8   8
365 2  22  22
366 2  22  22
367 2  17  17
368 2  20  20
369 2   5   5
370 2  14  14
371 2   4   4
  1 3   .   .
  2 3   0   0
  3 3   0   0
  4 3   0   0
  5 3   0   0
  6 3   0   0
  7 3   0   0
  8 3   1   1
  9 3   1   1
 10 3   2   2
 11 3   0   0
 12 3   0   0
 13 3   2   2
 14 3   1   1
 15 3   3   3
 16 3   3   3
 17 3   5   5
 18 3   4   4
 19 3   5   5
 20 3   9   9
 21 3  10  10
 22 3  11  11
 23 3  12  12
 24 3  23  23
 25 3  23  23
 26 3  22  22
 27 3  17  17
 28 3  30  30
 29 3  19  19
 30 3  28  28
 31 3  56  56
 32 3  23  23
 33 3  26  26
 34 3  28  28
 35 3  22  22
 36 3  31  31
 37 3  35  35
 38 3  25  25
 39 3  26  26
 40 3  54  54
 41 3  17  17
 42 3  25  25
 43 3  13  13
 44 3  18  18
 45 3  22  22
 46 3  17  17
 47 3  13  13
 48 3   7   7
 49 3  11  11
 50 3  13  13
 51 3  15  15
 52 3  18  18
 53 3  18  18
 54 3  21  21
 55 3  10  10
 56 3  12  12
 57 3  15  15
 58 3  12  12
 59 3  11  11
 60 3  12  12
 61 3   8   8
 62 3   9   9
 63 3   5   5
 64 3   5   5
 65 3   9   9
 66 3   6   6
 67 3   7   7
 68 3   5   5
 69 3   5   5
 70 3  11  11
 71 3   5   5
 72 3   4   4
 73 3   7   7
 74 3   5   5
 75 3   6   6
 76 3   4   4
 77 3   2   2
 78 3   4   4
 79 3   5   5
 80 3   2   2
 81 3   3   3
 82 3   3   3
 83 3   5   5
 84 3   2   2
 85 3   0   0
 86 3   2   0
 87 3   1   0
 88 3   3   0
 89 3   0   0
 90 3   3   0
 91 3   1   0
 92 3   1   0
 93 3   1   0
 94 3   0   0
 95 3   1   0
 96 3 -11   0
 97 3   1   0
 98 3   0   0
 99 3   0   0
100 3   0   0
101 3   0   0
102 3   1   0
103 3   1   0
104 3   2   0
105 3   0   0
106 3   0   0
107 3   1   0
108 3   0   0
109 3   0   0
110 3   1   0
111 3   0   0
112 3   0   0
113 3   0   0
114 3   1   0
115 3   0   0
116 3   0   0
117 3   0   0
118 3   0   0
119 3   0   0
120 3   0   0
121 3   0   0
122 3   0   0
123 3  -3   0
124 3   0   0
125 3   0   0
126 3   0   0
127 3   0   0
128 3   0   0
129 3  -4   0
130 3   0   0
131 3   0   0
132 3   0   0
133 3   0   0
134 3   0   0
135 3   0   0
136 3   0   0
137 3   0   0
138 3   0   0
139 3   0   0
140 3   0   0
141 3   0   0
142 3   0   0
143 3   0   0
144 3   0   0
145 3   0   0
146 3   0   0
147 3   0   0
148 3   0   0
149 3   0   0
150 3   0   0
151 3   0   0
152 3   0   0
153 3   0   0
154 3   0   0
155 3   0   0
156 3   0   0
157 3   0   0
158 3   0   0
159 3   0   0
160 3   0   0
161 3   0   0
162 3   0   0
163 3   0   0
164 3   0   0
165 3   0   0
166 3   0   0
167 3   0   0
168 3   0   0
169 3   0   0
170 3   0   0
171 3   0   0
172 3   0   0
173 3   0   0
174 3   0   0
175 3   0   0
176 3   0   0
177 3   0   0
178 3   0   0
179 3   0   0
180 3   0   0
181 3   0   0
182 3   0   0
183 3   0   0
184 3   0   0
185 3   0   0
186 3   0   0
187 3   0   0
188 3   0   0
189 3   0   0
190 3   0   0
191 3   0   0
192 3   0   0
193 3   1   1
194 3   0   0
195 3   0   0
196 3   0   0
197 3   0   0
198 3   0   0
199 3   0   0
200 3   1   1
201 3   0   0
202 3   0   0
203 3   0   0
204 3   0   0
205 3   0   0
206 3   0   0
207 3   0   0
208 3   0   0
209 3   0   0
210 3   0   0
211 3   0   0
212 3   0   0
213 3   0   0
214 3   0   0
215 3   1   1
216 3   0   0
217 3   0   0
218 3   0   0
219 3   0   0
220 3   0   0
221 3   0   0
222 3   0   0
223 3   1   1
224 3   1   1
225 3   0   0
226 3   0   0
227 3   0   0
228 3   0   0
229 3   0   0
230 3   0   0
231 3   0   0
232 3   0   0
233 3   0   0
234 3   0   0
235 3   0   0
236 3   0   0
237 3   0   0
238 3   3   3
239 3   0   0
240 3   1   1
241 3   1   1
242 3   1   1
243 3   2   2
244 3   1   1
245 3   2   2
246 3   1   1
247 3   2   2
248 3   2   2
249 3   1   1
250 3   4   4
251 3   6   6
252 3   2   2
253 3   4   4
254 3   6   6
255 3   1   1
256 3   2   2
257 3   3   3
258 3   8   8
259 3   5   5
260 3   7   7
261 3  10  10
262 3  10  10
263 3   6   6
264 3  11  11
265 3   8   8
266 3   9   9
267 3   8   8
268 3  11  11
269 3   9   9
270 3  11  11
271 3  11  11
272 3   7   7
273 3  12  12
274 3   8   8
275 3  13  13
276 3  10  10
277 3  10  10
278 3   9   9
279 3  12  12
280 3  13  13
281 3   9   9
282 3  12  12
283 3  13  13
284 3   9   9
285 3   7   7
286 3   9   9
287 3  13  13
288 3  10  10
289 3  18  18
290 3   9   9
291 3   9   9
292 3   6   6
293 3  11  11
294 3  14  14
295 3  10  10
296 3  14  14
297 3   9   9
298 3   9   9
299 3  13  13
300 3  10  10
301 3   7   7
302 3   8   8
303 3   7   7
304 3   4   4
305 3   9   9
306 3  12  12
307 3   6   6
308 3   5   5
309 3   8   8
310 3  11  11
311 3  11  11
312 3  14  14
313 3  10  10
314 3  13  13
315 3  13  13
316 3   8   8
317 3  19  19
318 3  13  13
319 3  14  14
320 3  15  15
321 3   9   9
322 3  13  13
323 3  13  13
324 3  12  12
325 3  12  12
326 3  18  18
327 3  10  10
328 3   9   9
329 3  11  11
330 3  11  11
331 3  16  16
332 3  15  15
333 3  16  16
334 3  11  11
335 3  12  12
336 3  10  10
337 3   9   9
338 3  11  11
339 3  15  15
340 3  17  17
341 3  13  13
342 3  16  16
343 3  11  11
344 3  12  12
345 3   8   8
346 3  10  10
347 3  15  15
348 3  16  16
349 3   6   6
350 3   9   9
351 3   8   8
352 3   8   8
353 3  12  12
354 3   8   8
355 3   7   7
356 3  11  11
357 3   8   8
358 3  12  12
359 3   4   4
360 3  15  15
361 3  17  17
362 3  17  17
363 3   8   8
364 3   7   7
365 3  11  11
366 3   9   9
367 3   5   5
368 3  10  10
369 3   5   5
370 3  16  16
371 3  12  12
  1 4   .   .
  2 4   0   0
  3 4   0   0
  4 4   0   0
  5 4   0   0
  6 4   0   0
  7 4   0   0
  8 4   0   0
  9 4   0   0
 10 4   0   0
 11 4   0   0
 12 4   0   0
 13 4   0   0
 14 4   0   0
 15 4   0   0
 16 4   0   0
 17 4   1   1
 18 4   0   0
 19 4   0   0
 20 4   0   0
 21 4   0   0
 22 4   1   1
 23 4   0   0
 24 4   1   1
 25 4   3   3
 26 4   1   1
 27 4   1   1
 28 4   1   1
 29 4   3   3
 30 4   7   7
 31 4   5   5
 32 4   4   4
 33 4   9   9
 34 4   4   4
 35 4   2   2
 36 4   7   7
 37 4   6   6
 38 4   3   3
 39 4   4   4
 40 4   7   7
 41 4  12  12
 42 4   9   9
 43 4   5   5
 44 4   4   4
 45 4   2   2
 46 4   3   3
 47 4   2   2
 48 4   0   0
 49 4   5   5
 50 4   3   3
 51 4   3   3
 52 4   3   3
 53 4   1   1
 54 4   1   1
 55 4   1   1
 56 4   1   1
 57 4   2   0
 58 4  -1   1
 59 4   1   1
 60 4   0   0
 61 4   2   2
 62 4   1   1
 63 4   1   1
 64 4   2   2
 65 4   2   2
 66 4   2   2
 67 4   0   0
 68 4   0   0
 69 4   0   0
 70 4   1   1
 71 4   1   1
 72 4   0   0
 73 4   0   0
 74 4   0   0
 75 4   0   0
 76 4   0   0
 77 4   0   0
 78 4   0   0
 79 4   1   1
 80 4   1   1
 81 4   0   0
 82 4   1   1
 83 4   0   0
 84 4   1   1
 85 4   0   0
 86 4   0   0
 87 4   0   0
 88 4   0   0
 89 4   0   0
 90 4   0   0
 91 4   0   0
 92 4   0   0
 93 4   0   0
 94 4   0   0
 95 4   0   0
 96 4   0   0
 97 4   0   0
 98 4   0   0
 99 4   0   0
100 4   0   0
101 4   0   0
102 4   0   0
103 4   0   0
104 4   1   1
105 4   0   0
106 4   0   0
107 4   0   0
108 4   0   0
109 4   0   0
110 4   0   0
111 4   0   0
112 4   0   0
113 4   0   0
114 4   0   0
115 4   0   0
116 4   1   1
117 4   1   1
118 4   0   0
119 4   0   0
120 4   0   0
121 4   0   0
122 4   0   0
123 4   0   0
124 4   0   0
125 4   0   0
126 4   0   0
127 4   0   0
128 4   0   0
129 4   0   0
130 4   0   0
131 4   0   0
132 4   0   0
133 4   0   0
134 4   0   0
135 4   0   0
136 4   0   0
137 4   0   0
138 4   0   0
139 4   0   0
140 4   0   0
141 4   0   0
142 4   0   0
143 4   0   0
144 4   0   0
145 4   0   0
146 4   0   0
147 4   0   0
148 4   0   0
149 4   0   0
150 4   0   0
151 4   0   0
152 4   0   0
153 4   0   0
154 4   0   0
155 4   0   0
156 4   0   0
157 4   0   0
158 4   0   0
159 4   0   0
160 4   0   0
161 4   0   0
162 4   0   0
163 4   0   0
164 4   0   0
165 4   0   0
166 4   0   0
167 4   0   0
168 4   0   0
169 4   0   0
170 4   0   0
171 4   0   0
172 4   0   0
173 4   0   0
174 4   0   0
175 4   0   0
176 4   0   0
177 4   0   0
178 4   0   0
179 4   0   0
180 4   0   0
181 4   0   0
182 4   0   0
183 4   0   0
184 4   0   0
185 4   0   0
186 4   0   0
187 4   0   0
188 4   0   0
189 4   0   0
190 4   0   0
191 4   0   0
192 4   0   0
193 4   0   0
194 4   0   0
195 4   0   0
196 4   0   0
197 4   0   0
198 4   0   0
199 4   0   0
200 4   0   0
201 4   0   0
202 4   0   0
203 4   0   0
204 4   0   0
205 4   0   0
206 4   0   0
207 4   0   0
208 4   0   0
209 4   0   0
210 4   0   0
211 4   0   0
212 4   0   0
213 4   0   0
214 4   0   0
215 4   0   0
216 4   0   0
217 4   0   0
218 4   0   0
219 4   0   0
220 4   0   0
221 4   0   0
222 4   0   0
223 4   0   0
224 4   0   0
225 4   0   0
226 4   0   0
227 4   0   0
228 4   0   0
229 4   0   0
230 4   0   0
231 4   0   0
232 4   0   0
233 4   0   0
234 4   0   0
235 4   0   0
236 4   0   0
237 4   0   0
238 4   0   0
239 4   0   0
240 4   0   0
241 4   0   0
242 4   3   3
243 4   0   0
244 4   3   3
245 4   1   1
246 4   1   1
247 4   2   2
248 4   1   1
249 4   5   5
250 4   4   4
251 4   2   2
252 4   6   6
253 4   5   5
254 4   1   1
255 4   1   1
256 4   8   8
257 4   6   6
258 4   7   7
259 4   3   3
260 4   2   2
261 4  11  11
262 4   7   7
263 4   6   6
264 4   3   3
265 4   6   6
266 4   8   8
267 4   5   5
268 4   4   4
269 4   2   2
270 4  12  12
271 4   7   7
272 4   3   3
273 4   4   4
274 4   5   5
275 4   2   2
276 4   5   5
277 4   5   5
278 4   0   0
279 4   8   8
280 4   1   1
281 4   4   4
282 4   2   2
283 4   5   5
284 4   2   2
285 4   2   2
286 4   4   4
287 4   3   3
288 4   2   2
289 4   4   4
290 4   4   4
291 4   1   1
292 4   3   3
293 4   4   4
294 4   2   2
295 4   0   0
296 4   3   3
297 4   4   4
298 4   2   2
299 4   3   3
300 4   1   1
301 4   2   2
302 4   0   0
303 4   0   0
304 4   2   2
305 4   1   1
306 4   0   0
307 4   1   1
308 4   2   2
309 4   2   2
310 4   2   2
311 4   1   1
312 4   0   0
313 4   2   2
314 4   2   2
315 4   3   3
316 4   1   1
317 4   0   0
318 4   1   1
319 4   0   0
320 4   0   0
321 4   0   0
322 4   1   1
323 4   2   2
324 4   1   1
325 4   0   0
326 4   0   0
327 4   0   0
328 4   1   1
329 4   1   1
330 4   1   1
331 4   3   3
332 4   1   1
333 4   1   1
334 4   0   0
335 4   0   0
336 4   1   1
337 4   0   0
338 4   0   0
339 4   1   1
340 4   1   1
341 4   0   0
342 4   1   1
343 4   0   0
344 4   1   1
345 4   0   0
346 4   1   1
347 4   0   0
348 4   1   1
349 4   1   1
350 4   1   1
351 4   1   1
352 4   1   1
353 4   0   0
354 4   0   0
355 4   1   1
356 4   0   0
357 4   1   1
358 4   0   0
359 4   0   0
360 4   0   0
361 4   0   0
362 4   0   0
363 4   1   1
364 4   0   0
365 4   0   0
366 4   1   1
367 4   0   0
368 4   0   0
369 4   0   0
370 4   0   0
371 4   0   0
  1 5   .   .
  2 5   0   0
  3 5   0   0
  4 5   0   0
  5 5   0   0
  6 5   0   0
  7 5   0   0
  8 5   1   1
  9 5   1   1
 10 5   2   2
 11 5   0   0
 12 5   0   0
 13 5   2   2
 14 5   1   1
 15 5   3   3
 16 5   3   3
end

[Clyde Schechter]

-rangerun- is not compatible with the -by:- prefix, but it does have a -by()- option, and that is what I was referring to.

As for what's going on in ID 3 at time 96, it's doing exactly what I said would be a problem in #2. When the code gets down to ID3 time 129, it encounters a -4. But it turns out that to get to a positive sum, you have to all the way back to time 88. Consequently, the replacement values that were originally generated for times 96 and 123 get clobbered. As I said, this probably isn't what you want, but honestly I can't figure out what a sensible solution to this situation is. If you have one, let me know and I'll try to modify the code to accommodate it.

[Demetrio Panarello]

Dear Clyde,
there are not so many anomalous values and I think the best solution is to manually adjust them, figuring out the best way to do so on a case-by-case basis.
I did so kind of quickly and the replacement variable is now perfectly suitable for my analyses.
Many thanks for your solution, which saved me lots of time and effort indeed!
Best,
Demetrio