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.
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;
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. 🙂
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.