CREATE A BINARY VARIABLE IF A VARIABLE <0 ON SEVERAL YEARS

[najiba etu]

Dear statalist,
I am trying to construct a binary variable.
This binary variable should take the value of 0 if the variable (TA hereafter) is inferior to zero more thant 3 years. Each TAN_X value correspond to a different year.
I think I should use the command count but I dont know how .
Does anyone know which code to use to do that ?
Thanks a lot
Najiba

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(company TAN_1 TAN_2 TAN_3 TAN_4 TAN_5)
1 -1  2 -3  2  2
2 -1 -2 -3 -5 -6
3 -4 -4 -4  5  5
4  3  3 -2  2  2
end

[Nick Cox]

I would reshape long and then the question and many others are easier to answer.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(company TAN_1 TAN_2 TAN_3 TAN_4 TAN_5)
1 -1  2 -3  2  2
2 -1 -2 -3 -5 -6
3 -4 -4 -4  5  5
4  3  3 -2  2  2
end

reshape long TAN_ , i(company) j(year)

egen count = total(TAN_ < 0), by(company)

gen wanted = count <= 2

list, sepby(company)

    +----------------------------------------+
     | company   year   TAN_   count   wanted |
     |----------------------------------------|
  1. |       1      1     -1       2        1 |
  2. |       1      2      2       2        1 |
  3. |       1      3     -3       2        1 |
  4. |       1      4      2       2        1 |
  5. |       1      5      2       2        1 |
     |----------------------------------------|
  6. |       2      1     -1       5        0 |
  7. |       2      2     -2       5        0 |
  8. |       2      3     -3       5        0 |
  9. |       2      4     -5       5        0 |
 10. |       2      5     -6       5        0 |
     |----------------------------------------|
 11. |       3      1     -4       3        0 |
 12. |       3      2     -4       3        0 |
 13. |       3      3     -4       3        0 |
 14. |       3      4      5       3        0 |
 15. |       3      5      5       3        0 |
     |----------------------------------------|
 16. |       4      1      3       1        1 |
 17. |       4      2      3       1        1 |
 18. |       4      3     -2       1        1 |
 19. |       4      4      2       1        1 |
 20. |       4      5      2       1        1 |
     +----------------------------------------+

[Bruce Weaver]

I do agree with Nick that generally speaking, many such tasks are easier with the data in long format. Having said that, the particular problem Najiba asked about can be addressed fairly easily in wide format too.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(company TAN_1 TAN_2 TAN_3 TAN_4 TAN_5)
1 -1  2 -3  2  2
2 -1 -2 -3 -5 -6
3 -4 -4 -4  5  5
4  3  3 -2  2  2
end

generate byte count = 0
foreach v of varlist TAN_1 - TAN_5 {
    replace count = count + (`v' < 0)
}
generate byte wanted = count <= 2
list

Output:

. list

     +------------------------------------------------------------------+
     | company   TAN_1   TAN_2   TAN_3   TAN_4   TAN_5   count   wanted |
     |------------------------------------------------------------------|
  1. |       1      -1       2      -3       2       2       2        1 |
  2. |       2      -1      -2      -3      -5      -6       5        0 |
  3. |       3      -4      -4      -4       5       5       3        0 |
  4. |       4       3       3      -2       2       2       1        1 |
     +------------------------------------------------------------------+

[najiba etu]

Thank you it worked perferctly