Determing surgeon volume within 365 days of previous operation

[Shad]

Happy Friday, hopefully I'm posting in the appropriate SAS forum. I'm working on a project and I'm not quite sure what the best approach is. Currently I'm trying to use arrays, but I'm struggling with getting the code right. Let me explain.

I'm trying to count the number of surgeries performed by a surgeon within the previous 365 days, to get a count of previous surgeries performed on the day of surgery.

It gets complicated because there can be multiple surgeons on a case. For instance, on 2/26/2009 surgeon 00-011 operated, at the time of this surgery I want to have his volume equal 0 since this is the "first obs" in the dataset. Then I would want volume to increase by 1 on 4/10/2009 since he had completed a prior surgery within 365 days. The next surgery was on 1/27/2011, not within 365 days of the previous so his volume at this time should be 0. However, a surgeon can also assist as a secondary or tertiary surgeon, and I want to count those in their volume when it is within 365 days of the previous operation, surgeon 00-011 was a secondary surgeon on 7/20/2011. Yet it wouldn't be counted on the 9/11/2012 since it was not within 365 days.

Pt_id	dos	          surg1    surg2   surg3
8	.			
9	.			
10	.			
11	.			
12	.			
13	.			
14	.			
15	.			
16	.			
17	.			
18	.			
20	.			
5	12/11/2013			
7	7/14/2014			
139	2/26/2009	00-011		
142	4/10/2009	00-011		
137	1/27/2011	00-011		
34	9/11/2012	00-011		
31	8/19/2014	00-011		
29	4/21/2015	00-011		
28	11/5/2015	00-011		
33	12/26/2012	00-012		
32	1/19/2014	00-012	00-018	
35	8/24/2011	00-013	00-018	
143	2/9/2009	00-014		
36	7/20/2011	00-014	00-011	
26	1/23/2016	00-014		
25	6/9/2016	00-014		
24	9/30/2016	00-016	00-011	
23	11/23/2016	00-016		
155	6/7/2016	00-022		
148	4/9/2013	00-023		
158	4/27/2017	00-023		
156	7/12/2016	00-024	00-021	00-022
157	9/27/2016	00-024		
81	8/17/2013	00-033		
145	3/9/2015	00-033		
146	10/27/2015	00-033		
147	7/20/2016	00-033		
153	3/31/2017	00-033		
144	8/8/2017	00-033		
2	8/1/2013	00-063		
4	11/29/2013	00-063		
1	2/13/2012	00-064		
3	10/4/2013	00-064		
39	12/21/2010	00-071		
40	5/24/2012	00-071		
38	1/16/2015	00-075		
37	8/16/2012	00-080		
41	2/12/2010	00-081	00-071	

It should look like this:

PT_id	dos	        surg1	surg2	surg3	Volume
8	.		.	.	.	.
9	.		.	.	.	.
10	.		.	.	.	.
11	.		.	.	.	.
12	.		.	.	.	.
13	.		.	.	.	.
14	.		.	.	.	.
15	.		.	.	.	.
16	.		.	.	.	.
17	.		.	.	.	.
18	.		.	.	.	.
20	.		.	.	.	.
5	12/11/2013	.	.	.	.
7	7/14/2014	.	.	.	.
139	2/26/2009	00-011	.	.	0
142	4/10/2009	00-011	.	.	1
137	1/27/2011	00-011	.	.	0
34	9/11/2012	00-011	.	.	1
31	8/19/2014	00-011	.	.	0
29	4/21/2015	00-011	.	.	1
28	11/5/2015	00-011	.	.	2
33	12/26/2012	00-012	.	.	0
32	1/19/2014	00-012	00-018	.	0
35	8/24/2011	00-013	00-018	.	0
143	2/9/2009	00-014	.	.	0
36	7/20/2011	00-014	00-011	.	0
26	1/23/2016	00-014	.	.	0
25	6/9/2016	00-014	.	.	1
24	9/30/2016	00-016	00-011	.	0
23	11/23/2016	00-016	.	.	1
155	6/7/2016	00-022	.	.	0
148	4/9/2013	00-023	.	.	0
158	4/27/2017	00-023	.	.	0
156	7/12/2016	00-024	00-021	00-022	0
157	9/27/2016	00-024	.	.	1

I've attached an excel with the 50 obs. Any advice/tips is greatly appreciated.

[tomrvincent]

I'd do a union like this:
select Pt_id, dos, surg1 as surg
union
select Pt_id, dos, surg2 as surg
where not (surg2 is missing)
union
select Pt_id, dos, surg3 as surg
where not (surg3 is missing)

Now you have the surgeon info in one table and can do your calcs. Join the table to itself finding any dos under 365 days on the record.

[Shad]

Hi Tom, 

Appreciate the suggestion, and I think that might work. 

If you're able to help me troubleshoot that'd be awesome. I'm struggling with getting it to accurately count the numbers of surgeries the way that I want it to. 

proc sql;
create table myTable as
select participant_id,	dos,	surgeon1_id from have
union
select participant_id,	dos,	surgeon_id_v2 from have
where not (surgeon_id_v2 is missing)
union
select participant_id,	dos,	surgeon_id_v3 from have
where not (surgeon_id_v3 is missing)
;
quit;

data want;
set mytable;
by surgeon1_id dos;
if first.surgeon1_id then do;
volume = 0;
end;
prev_surg = .;

prev_surg = lag(dos);

if dos - prev_surg <= 365 then volume + 1;
run;

Looking at surgeon 00-11, the first date should be 0 since that is the first operation. the next date should be 1, since it was within 365 days of the previous operation. Between 2009 and 2011 though it should reset to 0 since that date is not within 365 days of the previous operation. 

I'm sure the solution is right in front of me, but as a new SAS user I'm struggling. 🙂 

[Shad · Accepted solution]

This worked for me thought I would share it here. Thanks for putting me on the right trail. 

proc sql;
create table myTable as
select participant_id,	dos,	surgeon1_id from have
union
select participant_id,	dos,	surgeon_id_v2 from have
where not (surgeon_id_v2 is missing)
union
select participant_id,	dos,	surgeon_id_v3 from have
where not (surgeon_id_v3 is missing)
;
quit;

proc sort data = mytable; by surgeon1_id dos; run;

data want;
set mytable;
if surgeon1_id = '       ' then delete;
by surgeon1_id dos;
if first.surgeon1_id then do;

volume = 0;
end;

prev_surg = .;

prev_surg = lag(dos);

if prev_surg NE . then do;
if dos - prev_surg <= 365 then volume + 1;
else volume = 0; 
end;

run;