Counting sequential dates for providers on teams

[Harris Carmichael]

Hi all, long-time follower and this is the first time I can't find the answer to code in the prior posts. I'm sorry if my posting etiquette is lacking.

I have data for healthcare providers at several facilities and I need to know descriptive data about how many days providers work in a row on the same team (particularly how many days in a row providers work on average). Providers only work on one team at one facility per day but they could go to a different team (intra-facility or not) on the very next day.

I have facility ID, date (%td), team ID, provider name. It would look something like:

Facility	Census_DT	Team	Provider_nm
1	8/15/2023	1	John
1	8/16/2023	1	John
2	9/21/2023	3	John
2	9/22/2023	3	John
2	9/23/2023	3	John
1	7/1/2023	4	Mike
1	7/2/2023	4	Mike
1	7/3/2023	4	Mike
1	7/4/2023	4	Mike

I need to compare how many days providers work on the same team at the same facility in a row. Any thoughts?

Thank you for your time and consideration.

[Clyde Schechter]

Your description of your desired end results strikes me as vague. I'm not sure what you mean by "descriptive data" nor what the comparison contemplated is. But the key to both is identifying for each provider_nm, the number of consecutive days worked on a team. The following code will give you that, in the variable duration it creates, and will give you the average number of consecutive days on any team worked by each provider in the variable average_consec_days_per_team:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte facility float census_dt byte team str4 provider_nm
1 23237 1 "John"
1 23238 1 "John"
2 23274 3 "John"
2 23275 3 "John"
2 23276 3 "John"
1 23192 4 "Mike"
1 23193 4 "Mike"
1 23194 4 "Mike"
1 23195 4 "Mike"
end
format %td census_dt

by provider_nm team (census_dt), sort: gen int spell ///
    = sum(census_dt != census_dt[_n-1] + 1)
    
by provider_nm team spell (census_dt), sort: gen duration = _N
egen byte provider_team_tag = tag(provider_nm team)

by provider_nm: egen average_consec_days_per_team ///
    = mean(cond(provider_team_tag, duration, .))

Hopefully, with that available you can create whatever specific descriptive statistics and comparisons you need. If you need assistance with that, be sure to provide detailed and specific instructions as to what results you need.

Since you describe yourself as a long-time follower, I was surprised to see that you did not use -dataex- to show your example data. In the future, when showing data examples, please do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.