The first obs has an Events value of 1 because it is the only active event.
The second Events has a value of 2 because when event 2 started event 1 was still in progress.
The third Event has value of 3 because Events 1 and 2 were still active when obs 3 began. (Time1 for the third obs is < time2 for both obs 1 and obs2).
Event four has a value of 1 because a check of the Time2(s) above show all have ended prior to time1 for the fourth obs.
The goal is to know how many concurrent events are occurring during a given period, based on time1 being less than the time2 for all the obs prior.
The following code works (kind of, but it is the closet I gotten)
If time1 lt time2 then Events = ‘1’;
If time1 lt time2 and time1 lt lag(time2) then Events = ‘2’;
But this is where I’m stuck. It is possible that lag(time2) has concluded, but lag2(time2) and lag3(time2) have not, and the if…then statements don’t seem to be able to handle this.
Ultimately what I need to be able to do for each time1 is to be able to compare it again against each time2 “above” it and ascertain how many active events I have going on.
My attempts with loops, retain, sum, and lagX(time2) have not even come close, but my guess is they are all needed. My thought is I need someway to create a loop that says I'm on the 10th obs and need to check time2 obs 10, 9, 8, ... with sum+1 for each time it finds a time2 that is > the time1 for obs ten.
I hope this explanation has been clear and someone can point me in the right direction.
A couple of notes: Not every 'id' is included in the dataset because not every 'id' resulted in an event. Also, some 'id's could result in multiple events. And the actual dataset has the following date time format 01Jan09:13:00:00. I'm not sure if any of this matters for the solution, but I certainly want to make this as clear as possible.
Implicitly you will need to first sort your data to ensure that it is in proper sequence. My recommendation is to track your "minimum concurrent event" time with a RETAIN and a temporary SAS variable rather than using LAG.
Just to make sure I'm completely understand I should probably use Retain to capture and compare the current time1 and assign the time2 to a temporary variable and compare the temp vairable to the retain value?
For whatever your "concurent event matching" criteria, keep track of the oldest start-time (and consider that I don't know what you're doing about date-change?) and compare that time to the TIME1 and TIME2 (range) values of subsequent observations, I suppose.
You may want to map the process out on paper as a "stick figures" approach to developing a determination methodology and then apply that to the SAS language with your variables.
For every observation, you want to get the number of events unfinished at time1. I would sort the data after time1 variable. I don't think you could avoid keeping sequential the time2 of the unfinished events.
Thanks to Scott and Vasile for trying to help me with this.
After battIing this for days I just ended up just doing it in Excel. If anyone ever stops by here and is fluent in both vba and sas maybe they could help me with the translation, I'd really like to know a way to tackle this in SAS. I'd hoped SAS would put an end to jumping around between Access and Excel, but I guess they all have their place.