maximum within subset of group

[Costas Antoniou]

Hi all

I have a panel dataset that looks like:

Id date Var1 Var2
1 1 0 2
1 2 0 2
1 3 1 3
1 4 0 5
1 5 0 4
1 6 1 4
1 7 0 8
1 8 0 10

I would like to create a new variable, say Var3, that captures, within each id, the maximum value of Var2 from when var1=1 until the previous time Var1=1. So in this case it would be:

Id date Var1 Var2 Var3
1 1 0 2 .
1 2 0 2 .
1 3 1 3 max(3,5,9)=9
1 4 0 5 .
1 5 0 9 .
1 6 1 11 12
1 7 0 12 .
1 8 0 10 .


I have multiple id's in the dataset so this should be within each id.

Any help with this would be much appreciated.

best wishes to all

Costas

[Nick Cox]

Your examples are inconsistent on variable names and values. I've used the second.

I am interpreting

from when var1=1 until the previous time Var1=1

as

from when Var1 == 1 until (just before) the next time Var1 == 1 with an extra rule that end of panel also ends the spell

clear
input Id date Var1 Var2
1 1 0 2
1 2 0 2
1 3 1 3
1 4 0 5
1 5 0 9
1 6 1 11
1 7 0 12
1 8 0 10
end

bysort Id (date) : gen spell = sum(Var1)

bysort Id spell (date) : egen Var3 = max(Var2) if spell > 0

list, sepby(Id spell)


     +----------------------------------------+
     | Id   date   Var1   Var2   spell   Var3 |
     |----------------------------------------|
  1. |  1      1      0      2       0      . |
  2. |  1      2      0      2       0      . |
     |----------------------------------------|
  3. |  1      3      1      3       1      9 |
  4. |  1      4      0      5       1      9 |
  5. |  1      5      0      9       1      9 |
     |----------------------------------------|
  6. |  1      6      1     11       2     12 |
  7. |  1      7      0     12       2     12 |
  8. |  1      8      0     10       2     12 |
     +----------------------------------------+

See also https://journals.sagepub.com/doi/pdf...867X0700700209

ssc describe tsspell

[Costas Antoniou]

Thank you Nick, this works very well!