How to generate math loops taking a value of +1 if condition is satisfied and -1 if the condition is not missing?

[Jose Williams]

Hi,

I have panel data in a wide format for 5 rounds. I am trying to generate status mobility based on different statuses reported in each round.

I want to add those mobilities to create a final variable that adds the following to a persons mobility status: +1 if an upward mobility has happened, - 1 if a downward has happened and 0 if no switch has happened.

For example for a person:

1. From round 1 to round 2, there is no mobility- a value the assigned value should remain the same
2. From round 2 to round 3, there is upward mobility- value +1, should be added to the existing status
3. From round 3 to round 4, there is downward mobility- value -1, should be subtracted from the existing status
4. From round 4 to round 5, there is upward mobility- value +1, should be added to the existing status

Thus, the final value for this person should be the final total dividing by the number of reported status.

I tried the following:

gen mob1 = 1 if mobility2 > mobility1
gen mob2 = 1 if mobility3 > mobility2
gen mob3 = 1 if mobility4 > mobility3
gen mob4 = 1 if mobility5 > mobility4

replace mob1 = -1 if mobility2 < mobility1
replace mob2 = -1 if mobility3 < mobility2
replace mob3 = -1 if mobility4 < mobility3
replace mob4 = -1 if mobility5 < mobility4

egen row = rowtotal(mob1-mob4)

The above code won't give me this.

Sample of the data using dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mobility1 mobility2 mobility3 mobility4 mobility5)
. 1 1 1 1
. . . . .
. 1 1 1 1
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. 5 3 5 4
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
3 3 3 3 3
. . . . .
. . . . .
. . . 3 3
1 . 1 1 1
. . . . .
. . . . .
. . . . .
2 . 2 . 2
. . 1 . 1
1 . 1 . 1
. . . . .
. . . . .
1 1 1 1 1
. . . . 4
. . . . 3
1 1 1 1 1
1 1 2 1 3
2 2 1 . 2
1 1 1 1 .
. . 1 1 1
. . . . .
3 3 3 3 3
1 1 1 1 1
1 1 1 1 1
6 6 6 6 6
1 1 1 1 1
1 1 4 4 4
. . . . .
. . . . .
2 . . . 2
1 1 1 1 1
. 1 1 . 1
1 1 1 1 1
1 1 1 1 1
3 3 3 3 3
1 1 1 1 1
. 4 4 4 4
1 1 1 1 .
. . . . .
1 1 1 1 1
1 . . . .
. . . . .
. . . . .
3 . 3 3 2
1 . 1 1 1
4 . 4 3 4
. . . . .
. . . . .
5 5 5 5 5
1 1 1 1 1
. . 5 5 .
. 1 1 1 1
6 4 4 4 4
1 1 1 1 1
1 1 1 1 1
. . . . .
1 . . . .
1 . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. 3 6 . 3
. . . . .
. . . . .
. . . . .
1 1 . 1 1
1 1 . 1 1
1 1 . 1 1
. . . 1 .
. . . . .
. . . . .
. . . . .
1 1 . 1 1
. . . 1 1
. . . . .
5 5 5 5 5
1 1 1 1 1
. . . . .
5 5 . 4 .
. . . . .
5 5 5 5 5
end

[Nick Cox]

Your rules are incomplete, as they say nothing whatever about the right action for missing values.

1. If all values are missing, nothing can be said.

2. If all but one value is missing, nothing can be said about change.

That is still not complete. Here is a start on some code.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mobility1 mobility2 mobility3 mobility4 mobility5)
. 1 1 1 1
. . . . .
. 1 1 1 1
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. 5 3 5 4
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
3 3 3 3 3
. . . . .
. . . . .
. . . 3 3
1 . 1 1 1
. . . . .
. . . . .
. . . . .
2 . 2 . 2
. . 1 . 1
1 . 1 . 1
. . . . .
. . . . .
1 1 1 1 1
. . . . 4
. . . . 3
1 1 1 1 1
1 1 2 1 3
2 2 1 . 2
1 1 1 1 .
. . 1 1 1
. . . . .
3 3 3 3 3
1 1 1 1 1
1 1 1 1 1
6 6 6 6 6
1 1 1 1 1
1 1 4 4 4
. . . . .
. . . . .
2 . . . 2
1 1 1 1 1
. 1 1 . 1
1 1 1 1 1
1 1 1 1 1
3 3 3 3 3
1 1 1 1 1
. 4 4 4 4
1 1 1 1 .
. . . . .
1 1 1 1 1
1 . . . .
. . . . .
. . . . .
3 . 3 3 2
1 . 1 1 1
4 . 4 3 4
. . . . .
. . . . .
5 5 5 5 5
1 1 1 1 1
. . 5 5 .
. 1 1 1 1
6 4 4 4 4
1 1 1 1 1
1 1 1 1 1
. . . . .
1 . . . .
1 . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. 3 6 . 3
. . . . .
. . . . .
. . . . .
1 1 . 1 1
1 1 . 1 1
1 1 . 1 1
. . . 1 .
. . . . .
. . . . .
. . . . .
1 1 . 1 1
. . . 1 1
. . . . .
5 5 5 5 5
1 1 1 1 1
. . . . .
5 5 . 4 .
. . . . .
5 5 5 5 5
end

gen long id = _n 
egen nmiss = rowmiss(mobility?)
drop if inlist(nmiss, 4, 5)

gen wanted = 0 

forval j = 2/5 { 
    local J = `j' - 1 
    replace wanted = wanted + sign(mobility`j' - mobility`J') if mobility`j' < . & mobility`J' < . 
}

sort wanted id 
order id  
list, sepby(wanted) 

     +-----------------------------------------------------------------------------+
     |  id   mobili~1   mobili~2   mobili~3   mobili~4   mobili~5   nmiss   wanted |
     |-----------------------------------------------------------------------------|
  1. |   9          .          5          3          5          4       1       -1 |
  2. |  36          2          2          1          .          2       1       -1 |
  3. |  62          3          .          3          3          2       1       -1 |
  4. |  71          6          4          4          4          4       0       -1 |
     |-----------------------------------------------------------------------------|
  5. |   1          .          1          1          1          1       1        0 |
  6. |   3          .          1          1          1          1       1        0 |
  7. |  18          3          3          3          3          3       0        0 |
  8. |  21          .          .          .          3          3       3        0 |
  9. |  22          1          .          1          1          1       1        0 |
 10. |  26          2          .          2          .          2       2        0 |
 11. |  27          .          .          1          .          1       3        0 |
 12. |  28          1          .          1          .          1       2        0 |
 13. |  31          1          1          1          1          1       0        0 |
 14. |  34          1          1          1          1          1       0        0 |
 15. |  37          1          1          1          1          .       1        0 |
 16. |  38          .          .          1          1          1       2        0 |
 17. |  40          3          3          3          3          3       0        0 |
 18. |  41          1          1          1          1          1       0        0 |
 19. |  42          1          1          1          1          1       0        0 |
 20. |  43          6          6          6          6          6       0        0 |
 21. |  44          1          1          1          1          1       0        0 |
 22. |  48          2          .          .          .          2       3        0 |
 23. |  49          1          1          1          1          1       0        0 |
 24. |  50          .          1          1          .          1       2        0 |
 25. |  51          1          1          1          1          1       0        0 |
 26. |  52          1          1          1          1          1       0        0 |
 27. |  53          3          3          3          3          3       0        0 |
 28. |  54          1          1          1          1          1       0        0 |
 29. |  55          .          4          4          4          4       1        0 |
 30. |  56          1          1          1          1          .       1        0 |
 31. |  58          1          1          1          1          1       0        0 |
 32. |  63          1          .          1          1          1       1        0 |
 33. |  64          4          .          4          3          4       1        0 |
 34. |  67          5          5          5          5          5       0        0 |
 35. |  68          1          1          1          1          1       0        0 |
 36. |  69          .          .          5          5          .       3        0 |
 37. |  70          .          1          1          1          1       1        0 |
 38. |  72          1          1          1          1          1       0        0 |
 39. |  73          1          1          1          1          1       0        0 |
 40. |  85          1          1          .          1          1       1        0 |
 41. |  86          1          1          .          1          1       1        0 |
 42. |  87          1          1          .          1          1       1        0 |
 43. |  92          1          1          .          1          1       1        0 |
 44. |  93          .          .          .          1          1       3        0 |
 45. |  95          5          5          5          5          5       0        0 |
 46. |  96          1          1          1          1          1       0        0 |
 47. |  98          5          5          .          4          .       2        0 |
 48. | 100          5          5          5          5          5       0        0 |
     |-----------------------------------------------------------------------------|
 49. |  35          1          1          2          1          3       0        1 |
 50. |  45          1          1          4          4          4       0        1 |
 51. |  81          .          3          6          .          3       2        1 |
     +-----------------------------------------------------------------------------+

The person whose id here is 36 indicates one kind of difficulty. Their profile is 2 2 1 . 2 so on the information we have they went down and then up again. Should the code be different?

The simplest rule here is to calculate your measure only for people with information in all waves, but there is some loss of data in doing that.

[Jose Williams]

Nick Cox Thank you for your reply. Just a quick clarification, suppose I want to create a ticker-like thing based on mobility. For example in row 1 for ID 9:

The value is going down to 3 from 5, can I subtract -1? Then it is increasing, can I add +1, then again subtract -1? Then based on the final value, can I take a mean?
. 5 3 5 4

[Nick Cox]

I don't understand what needs clarification here. sign() produces results of -1 0 +1 according to the sign of the argument. See

help sign()

for what it does.

You can convert a total to a mean by dividing by the number of mobility comparisons.

If you ignore observations with any missing values, the divisor is automatically 4. Otherwise, you need to track by adding

gen comparisons = 0

before the loop and

replace comparisons = comparisons + !missing(mobility`j', mobility`J')

within the loop.