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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.