Rangestat using input from two different dates

[Costas Antoniou]

Hi all,

I’m working with a dataset of individual actions where each row represents a single action with:

• clientid: person ID
• UP: type of action (1 or 0 )
• open_time and close_time: datetimes when the action started and ended
• outcome_pct: outcome of the action

I would like to compute, for each action, rolling summary statistics (e.g., max, min mean, etc.) of previous action outcomes made by the same clientid and UP where the previous actions ended before the current action started, i.e.:

close_time of previous actions < open_time of the current action. Note that some actions are overlapping, so I cannot just sort by open_time.

Ideally, I’d like to do this efficiently with rangestat that can handle millions of actions.

Below is a small example dataset (15 observations) you can copy and paste to experiment with. Any guidance on how to implement this condition efficiently would be greatly appreciated!

Thank you, Costas

clear
input int clientid byte UP str19 open_time_str str19 close_time_str float outcome_pct
1 0 "01feb2016 09:00:00" "01feb2016 15:00:00" -5.0
1 1 "01feb2016 13:00:00" "01feb2016 20:00:00" -4.2
1 0 "01feb2016 17:00:00" "02feb2016 01:00:00" -3.4
1 1 "01feb2016 21:00:00" "02feb2016 06:00:00" -2.6
1 0 "02feb2016 01:00:00" "02feb2016 11:00:00" -1.8
2 0 "02feb2016 09:00:00" "02feb2016 15:00:00" -1.0
2 1 "02feb2016 13:00:00" "02feb2016 20:00:00" -0.2
2 0 "02feb2016 17:00:00" "03feb2016 01:00:00" 0.6
2 1 "02feb2016 21:00:00" "03feb2016 06:00:00" 1.4
2 0 "03feb2016 01:00:00" "03feb2016 11:00:00" 2.2
3 0 "03feb2016 09:00:00" "03feb2016 15:00:00" 3.0
3 1 "03feb2016 13:00:00" "03feb2016 20:00:00" 3.8
3 0 "03feb2016 17:00:00" "04feb2016 01:00:00" 4.6
3 1 "03feb2016 21:00:00" "04feb2016 06:00:00" 5.4
3 0 "04feb2016 01:00:00" "04feb2016 11:00:00" 6.2
end

* Convert to true %tc datetimes
gen double open_time = clock(open_time_str, "DMYhms")
gen double close_time = clock(close_time_str, "DMYhms")
format open_time close_time %tcDDmonCCYY_HH:MM:SS
drop open_time_str close_time_str
label var UP "Action type (0 or 1)"

list, sepby(clientid)

[Clyde Schechter]

rangestat   (max) max_outcome = outcome ///
            (min) min_outcome = outcome ///
            (mean) mean_outcome = outcome, ///
            by(clientid UP) interval(close_time . open_time)

Let's unpack this a little. The -by(clientid UP)- option assures that -rangestat- will only consider observations referring to the same clientid and UP. In rangestat's -interval()- option, the first argument always refers to something in the candidate observations being considered for including in the calculation of the requested statistics. The second and third arguments always refer to constants or values of variables in the current observation for which the statistics are being calculated. The interpretation of missing value as a second or third argument is the same is in Stata's -inrange()- function. So, for example, -interval(x . y)- will cause Stata to calculate the statistics from observations whose value of x is less than the current observation's value of y.

[Costas Antoniou]

Thank you Clyde for your answer. This works. I am wondering if there is a way to calculate rolling statistics with rangestat using some condition on previous rows that go into the calculation, like when UP=1 or UP=0. So, for example, for each row in the data (regardless if it is UP=1 or 0), to calculate two types of max and mins from previous rows, using all previous UP=1 rows and all previous UP=0 rows, so we would have max_outcome_UP1, and max_outcome_UP0.
Thank you again.
Costas

[Clyde Schechter]

-rangestat- itself does not accommodate imposing conditions of this kind. However, you can still accomplish the same result by first generating new variables that contain the values of outcome_pct only in the observations with the corresponding value of UP and missing in other observations, and then apply -rangestat- to that:

//  CREATE UP0 ONLY AND UP1 ONLY OUTCOMES VARIABLES
forvalues i = 0/1 {
    gen outcome_up`i' = cond(UP == `i', outcome_pct, .)
}

rangestat   (max) max_outcome_UP0 = outcome_up0 max_outcome_UP1 = outcome_up1 ///
            (min) min_outcome_UP0 = outcome_up0 min_outcome_UP1 = outcome_up1 ///
            (mean) mean_outcome_UP0 = outcome_up0 mean_outcome_UP1 = outcome_up1, ///
            by(clientid) interval(close_time . open_time)

Note, by the way, that in this code, UP does not appear in the -by()- option.

[Costas Antoniou]

Hi Clyde, yes this works perfectly, thank you.