I feel like this should be easy but I'm really struggling.
I have two tables, each has two columns:
Table 1: Start date, Stop date
Table 2: Count of surgeries, Day
I want to create a third (final) table that has Three columns; start date, stop date, sum of surgeries between those dates.
(Below tables have small date ranges for the sack of example but these would be much larger in practice)
Table 1 (I have):
Start | Stop |
7/3/2018 | 7/5/2018 |
7/5/2018 | 7/10/2018 |
Table 2 (I have):
Day | Count_of_Surgeries |
7/3/2018 | 21 |
7/4/2018 | 3 |
7/5/2018 | 12 |
7/6/2018 | 25 |
7/7/2018 | 31 |
7/8/2018 | 5 |
7/9/2018 | 1 |
7/10/2018 | 10 |
Table 3 (I want):
Start | Stop | Sum_surgeries |
7/3/2018 | 7/5/2018 | 36 |
7/5/2018 | 7/10/2018 | 72 |
Dates setup to include mid-night to mid-night so in actuality the "stop" date count is not actually included in the sum.
proc sql;
create table want as
select
t1.start,
t1.stop,
sum(t2.count_of_surgeries) as sum_surgeries
from t1 left join t2
on t1.start le day lt stop
group by t1.start, t1.stop
;
quit;
Untested; for tested code, please supply example data in usable form (data step with datalines, posted in a code box).
Dates setup to include mid-night to mid-night so in actuality the "stop" date count is not actually included in the sum.
Because of the above statement, I give a slightly modified output
proc format;
value datef
'03JUL2018'd-'04JUL2018'd = '03JUL2018-04JUL2018'
'05JUL2018'd-'09JUL2018'd = '05JUL2018-09JUL2018';
run;
proc summary data=have;
class day;
format day datef.;
var count_of_surgeries;
output out=_count_ sum=;
run;
If you have a longer table 1, you can turn this into a format without typing all the different dates via creating formats from the data set directly using the CNTLIN= option in PROC FORMAT. Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/p0owa4ftikc2ekn1q0rmpulg86cx.htm
Lastly ... advice ... 7/3/2018 could be July 3 or March 7, don't use this format, use 03JUL2018 which is unambiguous.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.