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.
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.
Ready to level-up your skills? Choose your own adventure.