conditional rangestat for rolling max

[Costas Antoniou]

Hi all

I have a dataset that looks like this

date id id2 x
1 1 A 1
2 1 A 2
1 1 B 6
2 1 B 9
1 1 C 3
2 1 C 0

I am using rangestat to find in a rolling way for each row the maximum and minimum values of x "seen" by id (I have many id's). However, the catch is that when looking back for each row, I want to exclude the cases where id2 equals the id2 of that row.

So, for example, for the second row (date=2 for id2=A), the set from which the maximum is drawn should exclude x=1 (date=1, id2=1A) and be drawn from (6,9,3,0, i.e., dates 1 and 2 when id2 is B or C).

Thank you very much!

Costas

[Nick Cox]

Please use dataex for data examples (FAQ Advice #12). Yours needed surgery to get it to work.

rangestat is from SSC, as you are asked to explain (also FAQ Advice #12).

You don't show the rangestat code you used (also ...).

This may well be soluble with rangerun from SSC, but I didn't get that working in a few minutes.

There is always brute force.

clear 
input date id str1 id2 x
1 1 A 1
2 1 A 2
1 1 B 6
2 1 B 9
1 1 C 3
2 1 C 0
end 

rangestat (min) min=x (max) max=x, interval(date . 0) by(id) 

local N = _N 

gen mymin = . 
gen mymax = . 

quietly forval i = 1/`N' { 
    su x if date <= date[`i'] & id == id[`i'] & id2 != id2[`i'], meanonly 
    replace mymin = r(min) in `i' 
    replace mymax = r(max) in `i' 
}

sort date id 

list, sepby(date)
    
    +-------------------------------------------------+
     | date   id   id2   x   min   max   mymin   mymax |
     |-------------------------------------------------|
  1. |    1    1     B   6     1     6       1       3 |
  2. |    1    1     C   3     1     6       1       6 |
  3. |    1    1     A   1     1     6       3       6 |
     |-------------------------------------------------|
  4. |    2    1     A   2     0     9       0       9 |
  5. |    2    1     B   9     0     9       0       3 |
  6. |    2    1     C   0     0     9       1       9 |
     +-------------------------------------------------+