Replacing numbers of ascending and descending order

[Philip Yang]

Hi all,

I have a simple question and data in the following form:

clear
input str9 team float action float time
"R" . 1
"R" . 2
"R" . 3
"R" . 4
"R" . 7
"R" 0 8
"R" . 9
"R" . 13
"R" . 25
"Z" . 2
"Z" . 3
"Z" . 4
"Z" . 6
"Z" . 7
"Z" 0 9
"Z" . 12
"Z" . 14
"Z" . 15
end

What I want to do is to replace the missing values with ascending and descending values from the zero for each team that then should like look like this:

clear
input str9 team float action float time
"R" -5 1
"R" -4 2
"R" -3 3
"R" -2 4
"R" -1 7
"R" 0 8
"R" 1 9
"R" 2 13
"R" 3 25
"Z" -5 2
"Z" -4 3
"Z" -3 4
"Z" -2 6
"Z" -1 7
"Z" 0 9
"Z" 1 12
"Z" 2 14
"Z" 3 15
end

Many thanks for you help!

Philip

[Nick Cox]

Here's one way to do it. For discussion of principles see https://www.stata.com/support/faqs/d...issing-values/

clear
input str9 team float action float time
"R" . 1
"R" . 2
"R" . 3
"R" . 4
"R" . 7
"R" 0 8
"R" . 9
"R" . 13
"R" . 25
"Z" . 2
"Z" . 3
"Z" . 4
"Z" . 6
"Z" . 7
"Z" 0 9
"Z" . 12
"Z" . 14
"Z" . 15
end

gen work = action
bysort team (time) : replace work = work[_n-1] + 1 if missing(work)
gsort team - time
by team: replace work = work[_n-1] - 1 if missing(work)
sort team time
list, sepby(team)

     +-----------------------------+
     | team   action   time   work |
     |-----------------------------|
  1. |    R        .      1     -5 |
  2. |    R        .      2     -4 |
  3. |    R        .      3     -3 |
  4. |    R        .      4     -2 |
  5. |    R        .      7     -1 |
  6. |    R        0      8      0 |
  7. |    R        .      9      1 |
  8. |    R        .     13      2 |
  9. |    R        .     25      3 |
     |-----------------------------|
 10. |    Z        .      2     -5 |
 11. |    Z        .      3     -4 |
 12. |    Z        .      4     -3 |
 13. |    Z        .      6     -2 |
 14. |    Z        .      7     -1 |
 15. |    Z        0      9      0 |
 16. |    Z        .     12      1 |
 17. |    Z        .     14      2 |
 18. |    Z        .     15      3 |
     +-----------------------------+

And another way to do it:

bysort team (time) : gen work = _n 
by team : egen origin = max(cond(action == 0, work, .))  
replace work = work - origin

The second solution assumes that 0 occurs at most once for each team.

For the egen device used there see section 9 in http://www.stata-journal.com/sjpdf.h...iclenum=dm0055

[Philip Yang]

Thank you very much Nick! It work perfectly. I have multiple times that the 0 occurs for each team in my data as well. This will probably be more challenging.

[Nick Cox]

If you have multiple values of 0, you need a rule for deciding what happens then.

This may get you started.

bysort team (time) : gen c_before = action 
bysort team (time) : gen c_after = action 
bysort team (time) : replace c_after = c_after[_n-1] + 1 if missing(c_after)
gsort team - time
by team: replace c_before = c_before[_n-1] - 1 if missing(c_before)
sort team time
list, sepby(team)

[Philip Yang]

Yes, thank you so much! A lot to think about :-)