Hi @land0220
Welcome to the community !
Here is an attempt to do that. Please test it.
Assumption: times are related to the same day; we can only make a link between the Staff and nurse / resident thanks to start/end time and encounter.
Best,
proc sql;
create table have2 as
select distinct coalesce(a.encounter, b.encounter) as encounter,
a.provider_name as staff,
case when b.prov_title = "Resident" then b.provider_name
else "" end as Resident,
case when b.prov_title = "Nurse" then b.provider_name
else "" end as Nurse,
sum((b.stop_time - b.start_time)/60) as mins
from (select * from have where prov_title = "Staff") as a
inner join
(select * from have where prov_title ne "Staff") as b
on a.encounter = b.encounter and
a.start_time <= b.start_time <= a.stop_time and
a.start_time <= b.stop_time <= a.stop_time
group by coalesce(a.encounter, b.encounter), a.provider_name, b.prov_title;
quit;
... View more
