sum of specific consecutive obs

[Socrates Kominakis]

Hello Everyone,

I use Stata 15.

The table below shows customer ID, year, and the value of transactions each customer did with two brands (brand1, and brand2).

I need to calculate the total value of transactions each customer did with each brand in each two consecutive years starting in 1990 (i.e., 1990-1991, 1992-1993, 1994-1995, and so on). That said, transactions in year 1991 should not be added to transactions in year 1992.

Then, I need to calculate the difference between the total transactions with brand1 and brand2, for each two consecutive years, for each customer (for example, transactions with brand1 in 1990-1991 minus transactions with brand2 in 1990-1991)

Thank you very much for your help.

id year brand1 brand2
1 1989 400 0
1 1990 100 50
1 1990 100 0
1 1991 100 0
1 2001 100 0
2 2000 250 0
2 2000 0 300
2 2000 0 690
2 2001 0 230
3 2009 890 0
3 2010 200 0
3 2011 0 2000
4 2013 120 0
4 2015 450 0
4 2015 400 230
4 2015 450 1200
4 2016 110 0

[Nick Cox]

Thanks for the data example.

clear 
input id year brand1 brand2
1 1989 400 0
1 1990 100 50
1 1990 100 0
1 1991 100 0
1 2001 100 0
2 2000 250 0
2 2000 0 300
2 2000 0 690
2 2001 0 230
3 2009 890 0
3 2010 200 0
3 2011 0 2000
4 2013 120 0
4 2015 450 0
4 2015 400 230
4 2015 450 1200
4 2016 110 0
end 

gen period = 2 * floor(year/2) 
collapse (sum) brand1 (sum) brand2, by(id period) 
gen diff = brand1 - brand2 

list, sepby(id) 

     +---------------------------------------+
     | id   period   brand1   brand2    diff |
     |---------------------------------------|
  1. |  1     1988      400        0     400 |
  2. |  1     1990      300       50     250 |
  3. |  1     2000      100        0     100 |
     |---------------------------------------|
  4. |  2     2000      250     1220    -970 |
     |---------------------------------------|
  5. |  3     2008      890        0     890 |
  6. |  3     2010      200     2000   -1800 |
     |---------------------------------------|
  7. |  4     2012      120        0     120 |
  8. |  4     2014     1300     1430    -130 |
  9. |  4     2016      110        0     110 |
     +---------------------------------------+

.

[Socrates Kominakis]

It worked perfectly.
Thank you so much for your help and your prompt response.