I will keep that in mind, thank you. ... View more

Ballardw   This works!   Fortunately, the grouping variable is the lowest dimension, so another join to a dim table will allow other aggregations for reporting. To your other comment, the structure of the command is conducive to automated code generation for an arbitrary array of variables, which could be executed in a variety of data frames.   Thank you for taking time to look at this ... you moved me along on my project! rg ... View more

Reeza, So in SAS, we have the luxury of alternative approaches with procs designed for certain tasks.  Part of the reason for this post is to help me move some POC development work into non-SAS environments.   But really, this problem is stumping me from a sql perspective ... and ugly would make me not feel so bad for not seeing the solution 🙂 rg     ... View more

Oh ... the data by design cannot have the upper = 1 without lower = 1. To your point, if there are two instruments making readings, an instrument failure could create that condition and the pump (in this example) would repeatedly turn on and off. The data is checked for completeness prior to event detection and I would expect to develop an imputation process for missing data, if present. Nonetheless, thanks for thinking ahead and providing such a succinct solution. I will be using it to develop a class of discretized events for my POC. ... View more

Thank you!   The code worked for this simulated data as well as the more complex events I am modeling.   That said, I am not one to ignore warnings :), what are your thinking about regarding the "many scenarios you need to consider about"?       ... View more

Thanks for the link on how to format questions for the Community environment.  I will review and apply to future posts.   That said,  the lower limit is 0 when it is out of range and the values could be reversed-coded if you prefer.  The problem is that while the upper limit is an explicit start of the event (turning on the pump), there is that curious state where the level is above the lower and below the upper limit.  In the curious state, if the pump is NOT on, then leave it OFF (not a critical level) and if the pump is ON, the leave it ON (to expected level of the lower limit).  The two-step limit approach may help minimize costs or risks associated with turning on the pump and associated systems based upon a single limit.   The problem as described is a general class of event detections and the example is simply an applied story to the data.  Nonetheless, the Target variable value is determined by the conditional evaluation of the previous state (lag(Target)) in order to use the upper or lower limit values to update the current state.   I am new to SAS and looking for help on how to program this Target variable; either in the datastep using retain statements or in ds2 using threading (or other methods unknown at this time).   Does this make sense? ... View more