topic Re: Sum based on columns in SAS Procedures

Sum based on columns

JenniferColeDH — Fri, 15 Oct 2021 16:23:42 GMT

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.

Re: Sum based on columns

Kurt_Bremser — Fri, 15 Oct 2021 16:30:57 GMT

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

Re: Sum based on columns

PaigeMiller — Fri, 15 Oct 2021 16:41:11 GMT

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.