Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count drought years for previous 7 years, by group

    I've got a dataset set up roughly like the below. The variable "drought" is equal to 1 if that year was a drought year in that district. I need to figure out to calculate droughtyears04, droughtyears05, droughtyears06, droughtyears07, which count how many of the 7 preceding years were drought years for 2004-2007. For example, droughtyears04 counts how many drought years there were between 1997-2003, droughtyears05 counts how many drought years there were between 1998-2004, so on and so forth.

    I'm not really sure how to do this. I would really appreciate the help (as always!)

    Also, it should be noted that the values can be carried through (i.e. droughtyears04 doesn't necessarily need to be missing for years other than 2004).
    region_id region_name drought year droughtyears04 droughtyears05 droughtyears06 droughtyears07
    1000 Vellore 0 1997
    1000 Vellore 1 1998
    1000 Vellore 0 1999
    1000 Vellore 0 2000
    1000 Vellore 1 2001
    1000 Vellore 1 2002
    1000 Vellore 0 2003
    1000 Vellore 0 2004 3
    1000 Vellore 1 2005 3
    1000 Vellore 0 2006 3
    1000 Vellore 1 2007 3
    254 Faizabad 1 1997
    254 Faizabad 1 1998
    254 Faizabad 0 1999
    254 Faizabad 0 2000
    254 Faizabad 0 2001
    254 Faizabad 1 2002
    254 Faizabad 1 2003
    254 Faizabad 0 2004 4
    254 Faizabad 1 2005 3
    254 Faizabad 0 2006 3
    254 Faizabad 1 2007 3
    256 Farrukhabad 1 1997
    256 Farrukhabad 1 1998
    256 Farrukhabad 1 1999
    256 Farrukhabad 0 2000
    256 Farrukhabad 1 2001
    256 Farrukhabad 1 2002
    256 Farrukhabad 0 2003
    256 Farrukhabad 0 2004 5
    256 Farrukhabad 1 2005 4
    256 Farrukhabad 0 2006 4
    256 Farrukhabad 1 2007 3
    Last edited by Nick Bas; 17 Apr 2019, 11:48.

  • #2
    Thanks for the data example. As is explicit in FAQ Advice #12 a dataex example is better, but that yields to very minor engineering.

    You can -- and I suggest should -- hold those results in a single variable. Here I use rangestat from SSC. You naturally may ignore the first seven years of record

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int region_id str11 region_name byte drought int year
    1000 "Vellore"     0 1997
    1000 "Vellore"     1 1998
    1000 "Vellore"     0 1999
    1000 "Vellore"     0 2000
    1000 "Vellore"     1 2001
    1000 "Vellore"     1 2002
    1000 "Vellore"     0 2003
    1000 "Vellore"     0 2004
    1000 "Vellore"     1 2005
    1000 "Vellore"     0 2006
    1000 "Vellore"     1 2007
     254 "Faizabad"    1 1997
     254 "Faizabad"    1 1998
     254 "Faizabad"    0 1999
     254 "Faizabad"    0 2000
     254 "Faizabad"    0 2001
     254 "Faizabad"    1 2002
     254 "Faizabad"    1 2003
     254 "Faizabad"    0 2004
     254 "Faizabad"    1 2005
     254 "Faizabad"    0 2006
     254 "Faizabad"    1 2007
     256 "Farrukhabad" 1 1997
     256 "Farrukhabad" 1 1998
     256 "Farrukhabad" 1 1999
     256 "Farrukhabad" 0 2000
     256 "Farrukhabad" 1 2001
     256 "Farrukhabad" 1 2002
     256 "Farrukhabad" 0 2003
     256 "Farrukhabad" 0 2004
     256 "Farrukhabad" 1 2005
     256 "Farrukhabad" 0 2006
     256 "Farrukhabad" 1 2007
    end
    
    rangestat (count) drought (sum) drought, int(year -7 -1) by(region_id)
    
    list, sepby(region_id)
    
         +---------------------------------------------------------------+
         | region~d   region_name   drought   year   droug~nt   drough~m |
         |---------------------------------------------------------------|
      1. |     1000       Vellore         0   1997          .          . |
      2. |     1000       Vellore         1   1998          1          0 |
      3. |     1000       Vellore         0   1999          2          1 |
      4. |     1000       Vellore         0   2000          3          1 |
      5. |     1000       Vellore         1   2001          4          1 |
      6. |     1000       Vellore         1   2002          5          2 |
      7. |     1000       Vellore         0   2003          6          3 |
      8. |     1000       Vellore         0   2004          7          3 |
      9. |     1000       Vellore         1   2005          7          3 |
     10. |     1000       Vellore         0   2006          7          3 |
     11. |     1000       Vellore         1   2007          7          3 |
         |---------------------------------------------------------------|
     12. |      254      Faizabad         1   1997          .          . |
     13. |      254      Faizabad         1   1998          1          1 |
     14. |      254      Faizabad         0   1999          2          2 |
     15. |      254      Faizabad         0   2000          3          2 |
     16. |      254      Faizabad         0   2001          4          2 |
     17. |      254      Faizabad         1   2002          5          2 |
     18. |      254      Faizabad         1   2003          6          3 |
     19. |      254      Faizabad         0   2004          7          4 |
     20. |      254      Faizabad         1   2005          7          3 |
     21. |      254      Faizabad         0   2006          7          3 |
     22. |      254      Faizabad         1   2007          7          3 |
         |---------------------------------------------------------------|
     23. |      256   Farrukhabad         1   1997          .          . |
     24. |      256   Farrukhabad         1   1998          1          1 |
     25. |      256   Farrukhabad         1   1999          2          2 |
     26. |      256   Farrukhabad         0   2000          3          3 |
     27. |      256   Farrukhabad         1   2001          4          3 |
     28. |      256   Farrukhabad         1   2002          5          4 |
     29. |      256   Farrukhabad         0   2003          6          5 |
     30. |      256   Farrukhabad         0   2004          7          5 |
     31. |      256   Farrukhabad         1   2005          7          4 |
     32. |      256   Farrukhabad         0   2006          7          4 |
     33. |      256   Farrukhabad         1   2007          7          3 |
         +---------------------------------------------------------------+
    Last edited by Nick Cox; 17 Apr 2019, 11:59.

    Comment


    • #3
      Much appreciated, Nick.

      Comment

      Working...
      X