Aggregation of pre-post treatment data (5 year intervals)

[Carrie Dolan]

I am trying to efficiently aggregate the IDA dollar (all_ida) amounts for the 5 year period before the crossover (i.e. the 5 years before crossover = 1) period, and then the 5 years immediately following (the first five years for which it equals 1). The data is at a project level and therefore there are duplicate years per country. Name_0 is the country name and Name_1 is the district name. Any suggestions are greatly appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 name_0 str31 name_1 int year float(crossover all_ida)
"Angola" "Bengo" 1995 0         0
"Angola" "Bengo" 1996 0         0
"Angola" "Bengo" 1997 0         0
"Angola" "Bengo" 1998 0         0
"Angola" "Bengo" 1999 0         0
"Angola" "Bengo" 2000 0         0
"Angola" "Bengo" 2001 0         0
"Angola" "Bengo" 2002 0         0
"Angola" "Bengo" 2003 0         0
"Angola" "Bengo" 2004 0         0
"Angola" "Bengo" 2005 0         0
"Angola" "Bengo" 2006 0         0
"Angola" "Bengo" 2007 0         0
"Angola" "Bengo" 2008 1         0
"Angola" "Bengo" 2009 1         0
"Angola" "Bengo" 2010 1     2.832
"Angola" "Bengo" 2011 1         0
"Angola" "Bengo" 2012 1         0
"Angola" "Bengo" 2013 1         0
"Angola" "Bengo" 2014 1         0
"Angola" "Bengo" 1995 0         0
"Angola" "Bengo" 1996 0         0
"Angola" "Bengo" 1997 0         0
"Angola" "Bengo" 1998 0         0
"Angola" "Bengo" 1999 0         0
"Angola" "Bengo" 2000 0         0
"Angola" "Bengo" 2001 0         0
"Angola" "Bengo" 2002 0         0
"Angola" "Bengo" 2003 0 2.0370371
"Angola" "Bengo" 2004 0         0
"Angola" "Bengo" 2005 0         0
"Angola" "Bengo" 2006 0         0
"Angola" "Bengo" 2007 0         0
"Angola" "Bengo" 2008 1         0
"Angola" "Bengo" 2009 1         0
"Angola" "Bengo" 2010 1     5.664
"Angola" "Bengo" 2011 1         0
"Angola" "Bengo" 2012 1         0
"Angola" "Bengo" 2013 1         0
"Angola" "Bengo" 2014 1         0
"Angola" "Bengo" 1995 0  2.666667
"Angola" "Bengo" 1996 0         0
"Angola" "Bengo" 1997 0         0
"Angola" "Bengo" 1998 0         0
"Angola" "Bengo" 1999 0         0
"Angola" "Bengo" 2000 0  3.666667
"Angola" "Bengo" 2001 0         0
"Angola" "Bengo" 2002 0         0
"Angola" "Bengo" 2003 0 3.8703704
"Angola" "Bengo" 2004 0         0
"Angola" "Bengo" 2005 0         0
"Angola" "Bengo" 2006 0         0
"Angola" "Bengo" 2007 0         0
"Angola" "Bengo" 2008 1         0
"Angola" "Bengo" 2009 1         0
"Angola" "Bengo" 2010 1     5.664
"Angola" "Bengo" 2011 1         0
"Angola" "Bengo" 2012 1         0
"Angola" "Bengo" 2013 1 4.1666665
"Angola" "Bengo" 2014 1         0
"Angola" "Bengo" 1995 0         0
"Angola" "Bengo" 1996 0         0
"Angola" "Bengo" 1997 0         0
"Angola" "Bengo" 1998 0         0
"Angola" "Bengo" 1999 0         0
"Angola" "Bengo" 2000 0         0
"Angola" "Bengo" 2001 0         0
"Angola" "Bengo" 2002 0         0
"Angola" "Bengo" 2003 0         0
"Angola" "Bengo" 2004 0         0
"Angola" "Bengo" 2005 0         0
"Angola" "Bengo" 2006 0         0
"Angola" "Bengo" 2007 0         0
"Angola" "Bengo" 2008 1         0
"Angola" "Bengo" 2009 1         0
"Angola" "Bengo" 2010 1         0
"Angola" "Bengo" 2011 1         0
"Angola" "Bengo" 2012 1         0
"Angola" "Bengo" 2013 1         0
"Angola" "Bengo" 2014 1         0
"Angola" "Bengo" 1995 0         0
"Angola" "Bengo" 1996 0         0
"Angola" "Bengo" 1997 0         0
"Angola" "Bengo" 1998 0         0
"Angola" "Bengo" 1999 0         0
"Angola" "Bengo" 2000 0         0
"Angola" "Bengo" 2001 0         0
"Angola" "Bengo" 2002 0         0
"Angola" "Bengo" 2003 0         0
"Angola" "Bengo" 2004 0         0
"Angola" "Bengo" 2005 0         0
"Angola" "Bengo" 2006 0         0
"Angola" "Bengo" 2007 0         0
"Angola" "Bengo" 2008 1         0
"Angola" "Bengo" 2009 1         0
"Angola" "Bengo" 2010 1         0
"Angola" "Bengo" 2011 1         0
"Angola" "Bengo" 2012 1         0
"Angola" "Bengo" 2013 1         0
"Angola" "Bengo" 2014 1         0
end

[Clyde Schechter]

What does aggregate mean? The total? The mean? The median? Something else?

And do you want the results separately per country, or per country/district pair, or just for the data set as a whole?

Also, your example data, the years where crossover == 1 are always 2008 through 2014. Is that true throughout your data, or does it vary by country? (Or even by district, though not among the particular districts shown?)

[Carrie Dolan]

The data set has 23 countries and the purpose of the aggregation is to create a sum of IDA funding levels at a district level for the five years pre and post crossover.

This data will be subsequently imported into ArcGIS to create pre and post maps for the 23 countries (46 maps total). The variable I am trying to create, which will be displayed in ArcGIS, is the sum of 5 years of IDA funding pre crossover and then 5 years of IDA funding post crossover.

The date of crossover varies by country (not district). The data is 1995-2014, but the year the country crossed over (meaning crossed over an income threshold) varies country to country.

[Clyde Schechter]

Thank you. I believe this does what you ask:

//    FIND YEAR OF CROSSOVER FOR EACH COUNTRY
by name_0, sort: egen cross_year = min(cond(crossover, year, .))

//    KEEP ONLY 5 YEARS BEFORE CROSSOVER & 5 YEARS OF FUNDING
keep if inrange(year, cross_year-5, cross_year+4)

//    AGGREGATE TO SUM OF IDA BY DISTRICT
collapse (sum) all_ida (first) cross_year,  by(name_0 name_1)

[Carrie Dolan]

Thank you. I appreciate the response. It is close. The first two parts do create a data set that has the pre and post data I need.

by name_0, sort: egen cross_year = min(cond(crossover, year, .)) 

keep if inrange(year, cross_year-5, cross_year+4)

The last portion called //AGGREGATE TO SUM OF IDA BY DISTRICT aggregates to a sum of IDA by district, but I need the sum of pre and the sum of post. Not pre/post together. The idea is that the two maps will be next to each other. One will show the spatial distribution of pre-crossover funding and the other will show the spatial distribution of post-crossover funding.

[Clyde Schechter]

OK, I misunderstood that. Change the final command to

collapse (sum) all_ida (first) cross_year, by(name_0 name_1 crossover)

[Carrie Dolan]

Thank you! That worked and your input is appreciated.