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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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