BookmarkSubscribeRSS Feed
JenniferColeDH
Calcite | Level 5

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):

StartStop
7/3/20187/5/2018
7/5/20187/10/2018

 

Table 2 (I have):

DayCount_of_Surgeries
7/3/201821

7/4/2018

3
7/5/201812
7/6/201825
7/7/201831
7/8/20185
7/9/20181
7/10/201810

 

Table 3 (I want):

StartStopSum_surgeries
7/3/20187/5/201836
7/5/20187/10/201872

 

Dates setup to include mid-night to mid-night so in actuality the "stop" date count is not actually included in the sum.

2 REPLIES 2
Kurt_Bremser
Super User
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).

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 960 views
  • 3 likes
  • 3 in conversation