calculate overlapping staffing intervals

[land0220]

New to the forum so I apologize if I'm not doing this right.  Using PCSAS 9.4.  I'm trying to create a report of how many minutes certain Staff doctors worked with Resident doctors or Nurses.  My raw data looks like this:

encounter	start_time	stop_time	provider_name	prov_title
1234	10:01am	11:56am	Dr. Apple	Staff
1234	10:01am	10:54am	Dr. Orange	Resident
1234	10:54am	11:10am	James Peach	Nurse
1234	11:10am	11:56am	Dr. Orange	Resident

What I want to end up with is a summary of how many minutes each provider worked with the others, by encounter:

encounter	staff	resident	nurse	mins
1234	Dr. Apple	Dr. Orange		99
1234	Dr. Apple		James Peach	16

I'm assuming some sort of RETAIN or ARRAY may be necessary, but I have little to no experience with these functions.  I appreciate any help!

[ed_sas_member]

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;

[land0220]

This gets me close!  Unfortunately, if an encounter has >1 Nurse, then all of the Nurses receive the same "mins" value (by Staff doc) rather than the mins for their respective time spent with the Staff doctor.  It looks something like this - where the 22 and 49 mins are how long each Staff doctor was on the case, rather than how long each Resident/Nurse was on the case with each Staff doc.

encounter	staff	resident	nurse	mins
8888	Dr. Red		Bob	22
8888	Dr. Red		Sally	22
8888	Dr. Blue		Bob	49
8888	Dr. Blue		Mary	49

On an aside, you noted that this only works if the times are on the same day.  Since procedures can occur over the span of more than one day (e.g. emergency surgery overnight) does that change the syntax of this code very much?

[ed_sas_member]

Hi @land0220 

You can remove b.provider_name from this clause:

	group by coalesce(a.encounter, b.encounter), b.provider_name, b.prov_title;

For you second point, how can the day be determined for procedures occurring over the span of more than one day?

[PaigeMiller]

data have2;
    set have;
    retain staff;
    if upcase(prov_title)='NURSE' then nurse=provider_name;
    if upcase(prov_title)='RESIDENT' then resident=provider_name;
    if upcase(prov_title)='STAFF' then staff=provider_name;
    mins=stop_time-start_time;
    drop provider_name prov_title;
run;

proc summary data=have2;
    class encounter staff resident nurse;
    types encounter*staff*resident encounter*staff*nurse;
    var mins;
    output out=want sum=;
run;

 This does the desired calculations. You can sort data set WANT to get it in the order you showed in your problem statement.

[novinosrin]

Hi @land0220  It's fairly straight forward in HASH i.e. if you are familiar

data have;
infile cards truncover;
input encounter	(start_time	stop_time) (:time8.)	provider_name & $12. prov_title $:12.;
format start_time	stop_time time8.;
cards;
1234	10:01am	11:56am	Dr. Apple	   Staff
1234	10:01am	10:54am	Dr. Orange	   Resident
1234	10:54am	11:10am	James Peach	   Nurse
1234	11:10am	11:56am	Dr. Orange	   Resident
;

data want;
 if _n_=1 then do;
   dcl hash H (multidata: "y") ;
   h.definekey  ("_n_") ;
   h.definedata ("s1", "s2", "prov_name","prov_title1") ;
   h.definedone () ;
   dcl hiter hi('h');
 end;
 do until(last.encounter);
  set have;
  by encounter;
  s1=start_time;
  s2=stop_time;
  if  prov_title ^= "Staff" then do;
    prov_name=provider_name;
	prov_title1=prov_title;
	h.add();
  end;
 end;
 do until(last.encounter);
  set have;
  by encounter;
  if prov_title = "Staff" then do;
   do while(hi.next()=0);
    if s1<start_time and s2<start_time or s1>stop_time and s2>stop_time then continue;
	t=s1-start_time;
	if t<=0 then t1=sum(abs(t),s1);
	else t1=sum(t,start_time);
	u=s2-stop_time;
	if u<=0 then u1=s2;
	else u1=s2-u;
	overlap=intck('minute',t1,u1);
	output;
	call missing(of u:,of t:);
   end;
  end;
 end;
 h.clear();
 keep encounter provider_name prov_title prov_title1 prov_name Overlap;
run;

/*Summary*/
proc summary data=want nway noprint;
 class encounter provider_name prov_title prov_title1 prov_name;
 var overlap;
 output out=summary(drop=_:) sum=;
run;

Note: If you say your knowledge of HASH is sound, I can combine PROC SUMMARY step and HASH Double DOW into one. Just that code maintenance and editing would get harder let alone strain to our eyes.

[novinosrin · Accepted solution]

Hi again @land0220  Here is a PROC SQL solution

data have;
infile cards truncover;
input encounter	(start_time	stop_time) (:time8.)	provider_name & $12. prov_title $:12.;
format start_time	stop_time time8.;
cards;
1234	10:01am	11:56am	Dr. Apple	   Staff
1234	10:01am	10:54am	Dr. Orange	   Resident
1234	10:54am	11:10am	James Peach	   Nurse
1234	11:10am	11:56am	Dr. Orange	   Resident
;

proc sql;
create table want as
select encounter, provider_name, prov_title, prov_title1, prov_name, sum(overlap) as Overlap
from
(select a.encounter, a.provider_name, a.prov_title,
b.prov_title as prov_title1,b.provider_name as prov_name,
a.start_time,a.stop_time,
ifn(b.start_time-a.start_time<=0,sum(abs(b.start_time-a.start_time),b.start_time),
sum(b.start_time-a.start_time,a.start_time)) as t1,
ifn(b.stop_time-a.stop_time<=0,b.stop_time,b.stop_time-(b.stop_time-a.stop_time)) as u1,
intck('minute',calculated t1,calculated u1) as Overlap

from have(where=(prov_title = "Staff")) a ,have(where=(prov_title ^= "Staff")) b
where a.encounter=b.encounter and
not (b.start_time<a.start_time and b.stop_time<a.start_time or
b.start_time>a.stop_time and b.start_time>a.stop_time))
group by encounter ,provider_name, prov_title, prov_title1, prov_name
order by a.encounter, a.provider_name, a.prov_title,prov_title1,prov_name;
quit;

[PaigeMiller]

Again, I am concerned about the proliferation of very complex SQL to achieve a solution to this problem, or using complex (even though it was called "fairly straight forward", I consider it complex) hash solutions for this problem, which can be solved with a DATA step and PROC SUMMARY.

Especially when the questioner is likely to be a relatively new SAS programmer, I think we as a community ought to give solutions that use relatively simple tools that a new SAS programmer might be able to grasp (well, tools as simple as can be for the problem stated; obviously some problems — not this one — do require complex solutions involving one or more of hash, complex SQL, macros, etc.)

I also ask a question ... it was my understanding that hash solutions were designed to overcome performance problems, rather than a tool that is to be applied to any data re-arranging problem. Is that a correct understanding?

[novinosrin]

Good morning Sir @PaigeMiller    I quite agree and second to your thoughts and understanding. Of course, it's always better to deliver just what the doctor ordered. I admit we the "frequent users" should be mindful of the needs of the OP.

Nonetheless, unlike this OP where he/she clearly mentioned "I'm assuming some sort of RETAIN or ARRAY may be necessary, but I have little to no experience with these functions.  I appreciate any help!"  ,which I should have taken note of and been mindful, some OPs give mixed signals as they seem to have the idea of the concepts and often pick fancy solutions let alone that goes into a wheel-spin getting back to the community with  follow up questions soon as a change is required.  So in this case, I suppose the intent is perhaps to experiment something they know but unable to practically implement.

Plus , sometimes the New user/other contributor tags seem to not reflect the real experience/knowledge of the user and that is something very difficult to tell. For example, @ed_sas_member    impresses me so much having not been in the community long. Well, I trust this can only be clarified by @ShelleySessoms(if she can lend a moment)    as to what the tags mean to understand and make an assumption of the likely solutions that the OP is after. 

With respect to your understanding of HASH, you are certainly correct but here it basically adds to the buffet menu. I give in to you for the clear reasoning. 

[ed_sas_member]

Thank you @novinosrin 🙂
This pleases me as I am in professional retraining since a couple of months to become a SAS programmer and I learn a lot from the community to achieve this.
You, Kurt, Paige, ... impress me so much !!

[ShelleySessoms]

Happy to jump in here @novinosrin. By "tags" I believe you mean the rankings associated with the user (new user, super contributor, super user). If that is the case, those rankings are assigned (and change) based on experiences within the community, not with SAS itself. So I would not use those rankings to make any assumptions about an OP's experience with SAS software.

Best,

Shelley 

[novinosrin]

Thank you. So may be this opens a case to include a point in the "How to ask a good question"  to mention OP's experience/knowledge levels. 

[land0220]

For what it's worth, I'm a "New User" to posting in this forum, but I'm not a new user of SAS.  I certainly wouldn't say my skills are advanced, but I'm definitely not a beginner, if that makes sense.  I've used this forum for years to help me solve "problems" in SAS, and this is the first time I couldn't find a solution for my need, hence the post.  I hope I didn't cause any problems.

The PROC SQL solutions both did what I needed (had to tweak the first one a bit).  I'm not familiar with HASH so I didn't try that.  I'd also be open to non-SQL methods (DATA step) if someone has any to suggest.  I'm happy to have found a solution that works, but open to learning more than one method to achieve the results I desire.  Thanks to everyone for your help!

[novinosrin]

Hey @land0220  To your "I hope I didn't cause any problems." Chilax, I hope didn't mention anything that gave rise to your thinking of  causing problem. Jesus!, is my communication that bad. The context of the discussion was about how to help OP's the best way we can rather than throw up everything just because there is a possibility. I hope you get the point.

The community managers and users(you and me) are continuously improving how to make this forum a better place to ask, find and share. 🙂  I will have a pint for you. 

---

@land0220 wrote:

For what it's worth, I'm a "New User" to posting in this forum, but I'm not a new user of SAS.  I certainly wouldn't say my skills are advanced, but I'm definitely not a beginner, if that makes sense.  I've used this forum for years to help me solve "problems" in SAS, and this is the first time I couldn't find a solution for my need, hence the post.  I hope I didn't cause any problems.

The PROC SQL solutions both did what I needed (had to tweak the first one a bit).  I'm not familiar with HASH so I didn't try that.  I'd also be open to non-SQL methods (DATA step) if someone has any to suggest.  I'm happy to have found a solution that works, but open to learning more than one method to achieve the results I desire.  Thanks to everyone for your help!

---

[land0220]

I'm quite "chilaxed" over here actually, just trying to tread lightly in my first post/thread on the board, that's all. 

Thanks again for your solution - did exactly what I needed to do!