Counting event occurences and duration

[Alyssa Beavers]

I have data in long form that contains event occurences I have one row per event, and the length of the event in days is indicated by the variable "Duration." There are multiple types of events in the datasets; type is indicated in the EventType variable and the below dataex has two event types: OSS and DET (there are more event types but for simplicity I just included those 2). For each person, varname "ID", I am trying to create a dataset that has new variables with counts of occurences of each type of event, as well as new variables with the total duration of each event type. So, my question is, how do I create a dataset that has the below variables:

ID OSS_Count OSS_Duration DET_Count DET_Duration

where OSS_Count is the number of rows an ID had with EventType "OSS" and OSS_Duration is the sum of Duration that ID had for rows with EventType OSS, and so on for the other EventTypes?

Thanks in advance!
-Alyssa Beavers

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID byte Duration str3 EventType
123456 3 "OSS"
123456 2 "OSS"
123457 1 "OSS"
123458 1 "OSS"
123459 1 "OSS"
123460 6 "OSS"
123460 1 "OSS"
123461 2 "DET"
123456 4 "DET"
123456 1 "DET"
end

[George Ford]

foreach type in OSS DET {
egen `type'_count = count(cond(EventType=="`type'",Duration,.)) , by(ID)
egen `type'_duration = sum(cond(EventType=="`type'",Duration,.)) , by(ID)
}

[Alyssa Beavers]

Hi George Ford

Thanks for your code! I did run into a snag with this code when I tried to use it on some of my other datasets. Some of my other datasets has some missing values for the "Duration" variable, and when that happens, they don't get counted in my _count variables. I have made a new dataex below where I have replaced some of the durations with missing. If you or someone else is able to take a shot at this that would be much appreciated!

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID byte Duration str3 EventType
123456 . "OSS"
123456 2 "OSS"
123457 1 "OSS"
123458 1 "OSS"
123459 1 "OSS"
123460 6 "OSS"
123460 1 "OSS"
123461 2 "DET"
123456 . "DET"
123456 1 "DET"
end

[George Ford]

There are probably several ways to do this, but this should work. And ID_N may be useful.

bys ID: g ID_N = _n
foreach type in OSS DET {
    egen `type'_count = count(cond(EventType=="`type'" , ID_N,.)) , by(ID)
    egen `type'_duration = sum(cond(EventType=="`type'" , Duration,.)) , by(ID)
}

[George Ford]

Or just count ID.