Expand dataset to duration

[Henry Strawforrd]

I have a panel of i groups and t periods for a variable y.

I want to expand it so that it has two t dimension tbeg and tend. Then y should be the cumulative sum within the range tbeg and tend. See the example below.

How to accomplish this?

// Starting dataset (i are groups, t are time periods, y is the variable to be summed)
clear
input i t y
1 1 1
1 2 1
1 3 1
2 1 0
2 2 0
2 3 1
end

// Ending dataset
clear
input i tbeg tend y
1 1 1 1
1 1 2 2
1 1 3 3
1 2 2 1
1 2 3 2
1 3 3 1
2 1 1 0
2 1 2 0
2 1 3 1
2 2 2 0
2 2 3 1
2 3 3 1
end

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(i t y)
1 1 1
1 2 1
1 3 1
2 1 0
2 2 0
2 3 1
end

bys i (t): gen toexpand= t[_N]-t+1
rename t t_start
expand toexpand
bys i t_start: gen t_end= t_start[1]+_n-1, after(t_start)
drop toexpand

Res.:

. l, sep(0)

     +-------------------------+
     | i   t_start   t_end   y |
     |-------------------------|
  1. | 1         1       1   1 |
  2. | 1         1       2   1 |
  3. | 1         1       3   1 |
  4. | 1         2       2   1 |
  5. | 1         2       3   1 |
  6. | 1         3       3   1 |
  7. | 2         1       1   0 |
  8. | 2         1       2   0 |
  9. | 2         1       3   0 |
 10. | 2         2       2   0 |
 11. | 2         2       3   0 |
 12. | 2         3       3   1 |
     +-------------------------+

[Henry Strawforrd]

Thanks! However, the result does not seem correct. I would expect y to be 2 for i=1 and t = 1-2 and for t=1-3 it should be 3. The dataset have the sum of y between the two dates.

[Henry Strawforrd]

Ah, I guess one more line is enough to achieve it

bysort i t_start (t_end): gen ycumul = sum(y)

[Henry Strawforrd]

No, there is another issue when y can vary

Suppose this example

// Starting dataset (i are groups, t are time periods, y is the variable to be summed)
clear
input i t y
1 1 1
1 2 2
1 3 3
2 1 0
2 2 0
2 3 1
end

bys i (t): gen toexpand= t[_N]-t+1
rename t t_start
expand toexpand
bys i t_start: gen t_end= t_start[1]+_n-1, after(t_start)
drop toexpand
bysort i t_start (t_end): gen ycumul = sum(y)

provides this again. It overwrote the y in later periods it seems.

i t_start t_end y ycumul
1 1 1 1 1
1 1 2 1 2
1 1 3 1 3
1 2 2 2 2
1 2 3 2 4
1 3 3 3 3
2 1 1 0 0
2 1 2 0 0
2 1 3 0 0
2 2 2 0 0
2 2 3 0 0
2 3 3 1 1

[Andrew Musau]

I am not clear on what you mean by

It overwrote the y in later periods it seems.

How should the result look like in #5?

[Henry Strawforrd]

Always the sum of y within i between t_start and t_end

i t_start t_end y ycumul
1 1 3 1 6


The sum between period 1 and period was 6 since it y=1 in t=1, y=2 in t=2, and y=3 in t=3.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(i t y)
1 1 1
1 2 1
1 3 1
2 1 0
2 2 0
2 3 1
end

bys i (t): gen toexpand= t[_N]-t+1
rename t t_start
expand toexpand
bys i t_start: gen t_end= t_start[1]+_n-1, after(t_start)
drop toexpand
bysort i t_start (t_end): gen ycumul = cond(_n!=1, 0, y)
by i: replace ycumul= sum(ycumul)

Res.:

. l, sepby(i)

     +----------------------------------+
     | i   t_start   t_end   y   ycumul |
     |----------------------------------|
  1. | 1         1       1   1        1 |
  2. | 1         1       2   1        1 |
  3. | 1         1       3   1        1 |
  4. | 1         2       2   1        2 |
  5. | 1         2       3   1        2 |
  6. | 1         3       3   1        3 |
     |----------------------------------|
  7. | 2         1       1   0        0 |
  8. | 2         1       2   0        0 |
  9. | 2         1       3   0        0 |
 10. | 2         2       2   0        0 |
 11. | 2         2       3   0        0 |
 12. | 2         3       3   1        1 |
     +----------------------------------+

[Henry Strawforrd]

hm, again not quite. You see that t_start=3 and t_end=3 is 3 while it would be 1. Its tricky!

[Andrew Musau]

Calculate the sums before expanding the dataset.