Counting events for continuous years using loops

[Alberto Ramon]

Hi Statalisers,

I have been a frequent user, but this is my first time posting. I'm using Stata 14.2 and I have a dataset of conflict events that contains 14,000 observations with seven variables. I want to create one variable that counts the total events by year for each country. As you see there is already a variable that does this (tot_yr_inc). However, some are multi-year events so I want to count those events as part of the total events for that continuing year (this is what the variable is for new_tot) . For example, in the eight observation the start year (styr) is 2002 but the end year (eyr) is 2004 so the total for 2003 should be 5 (not 4) and the total for 2004 should be 4 (not 3).

The variables are defined as:
country - the country the event occurred
styr - The year the event started
eyr - The year the event ended
yr_id - An identifier for the year-country pair
yr_inc_num - The incident number for that year (Used as a count for the events of the year)
tot_yr_inc - Total events for that year-country pair
new_tot - New variable created to account for the continuous events

The snippet of my data is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long country int(styr eyr) float(yr_id yr_inc_num tot_yr_inc new_tot_yr_inc)
10 2000 2000 1 1 3 3
10 2000 2000 1 2 3 3
10 2000 2000 1 3 3 3
10 2001 2001 2 1 2 2
10 2001 2001 2 2 2 2
10 2002 2002 3 1 4 4
10 2002 2002 3 2 4 4
10 2002 2004 3 3 4 4
10 2002 2002 3 4 4 4
10 2003 2003 4 1 4 4
10 2003 2003 4 2 4 4
10 2003 2003 4 3 4 4
10 2003 2003 4 4 4 4
10 2004 2004 5 1 3 3
10 2004 2004 5 2 3 3
10 2004 2004 5 3 3 3
12 1990 1990 6 1 3 3
12 1990 1990 6 2 3 3
12 1990 1990 6 3 3 3
12 1991 1991 7 1 3 3
12 1991 1992 7 2 3 3
12 1991 1991 7 3 3 3
12 1992 1992 8 1 2 2
12 1992 1992 8 2 2 2
12 1993 1993 9 1 2 2
12 1993 1993 9 2 2 2
end
label values country country
label def country 10 "Cote d'Ivoire", modify
label def country 12 "Democratic Republic of the Congo", modify

I have been trying to do this using a loop and the code runs without any errors, but nothing changes. I have messed with it and understand why it doesn't work but at this point I am stuck. I am not sure what else to do. I would greatly appreciate your help.

forval c=1/9 {
               if styr<eyr & yr_id==`c'{
               local pair=eyr if yr_id==`c'
               local group=yr_id if `pair'==`pair' 
               replace new_tot=new_tot + 1 if yr_id>`c' & yr_id<`group'
               }
                else {
               }
    }

[William Lisowski]

Welcome to Statalist, and thank you for the clear sample data.

Starting with the sample data, here's a different approach to counting the number of incidents active in each year. I think it will aim you in a useful direction.

generate nyr = eyr-styr+1
rename styr year
generate seq = _n
keep country seq year nyr
expand nyr
bysort country seq: replace year = year+_n-1
collapse (count) tot_yr_inc=seq, by(country year)
list, noobs sepby(country)

. list, noobs sepby(country)

  +----------------------------------------------------+
  |                          country   year   tot_yr~c |
  |----------------------------------------------------|
  |                    Cote d'Ivoire   2000          3 |
  |                    Cote d'Ivoire   2001          2 |
  |                    Cote d'Ivoire   2002          4 |
  |                    Cote d'Ivoire   2003          5 |
  |                    Cote d'Ivoire   2004          4 |
  |----------------------------------------------------|
  | Democratic Republic of the Congo   1990          3 |
  | Democratic Republic of the Congo   1991          3 |
  | Democratic Republic of the Congo   1992          3 |
  | Democratic Republic of the Congo   1993          2 |
  +----------------------------------------------------+

[Alberto Ramon]

William, thank you so much. This is exactly what I needed. You just saved me days of work. I had not used expand before but I am definitely adding it to my tool box.

Alberto