Hi How to sum rows by date that is between specific dates

[shasank]

Hi SAS Community, 

I need you help for the below problem. 

The logic needed is to sum costs(Paid and OOP) between Start_line1 to Stop_line1 based on Start date. I would also do the same to Start_line2 and Stop_line2. 

PID	Start_line1	Stop_line1	Start_line2	Stop_line2	Start date	Paid	OOP
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	27-Aug-13	2161.01	2479.51
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	20-Sep-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	18-Oct-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	20-Nov-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	18-Dec-13	6630.48	348.98
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	13-Jan-14	2148.1	2896.24
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	10-Feb-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	21-Mar-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	16-Apr-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	14-May-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	13-Jun-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	8-Jul-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	8-Aug-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	9-Sep-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	12-Oct-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	11-Nov-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	10-Feb-15	2506.27	2964.14
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	17-Feb-15	25407.66	0

Expected Outcome: 

PID	Start_line1	Stop_line1	Paid	OOP
1	1-Aug-13	30-Dec-14	96150.62	10209.52
PID	Start_line2	Stop_line2	Paid	OOP
1	1-Dec-14	10-Jan-15	0	0

[novinosrin · Accepted solution]

Hi @shasank  Please repeat the solution for your line2 condition coz i'm feeling lazy

data have;
input PID	(Start_line1	Stop_line1	Start_line2	Stop_line2	Startdate) (:date9.)	Paid	OOP;
format Start_line1	Stop_line1	Start_line2	Stop_line2	Startdate date9.;
cards;
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	27-Aug-13	2161.01	2479.51
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	20-Sep-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	18-Oct-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	20-Nov-13	6139.41	323.13
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	18-Dec-13	6630.48	348.98
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	13-Jan-14	2148.1	2896.24
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	10-Feb-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	21-Mar-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	16-Apr-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	14-May-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	13-Jun-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	8-Jul-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	8-Aug-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	9-Sep-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	12-Oct-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	11-Nov-14	6679.28	351.54
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	10-Feb-15	2506.27	2964.14
1	1-Aug-13	30-Dec-14	1-Dec-14	10-Jan-15	17-Feb-15	25407.66	0
;

data expected;
 do until(last.pid);
  set have;
  by pid;
  if  Start_line1<=Startdate<=Stop_line1 then do;
   sum_paid=sum(sum_paid,paid);
   sum_oop=sum(oop,sum_oop);
  end;
 end;
 drop paid oop;
run;

[shasank]

Hi @novinosrin. Thank you so much.

[PGStats]

If both sums will reside in the same columns, in the same dataset, they will have the same name. In this SQL solution, I added the variable line to tell the sums apart:

proc sql;
create table want as
select
    pid,
    start_line1 as start_line format=date9., stop_line1 as stop_line format=date9., 1 as line,
    coalesce(sum(case when startdate between start_line1 and stop_line1 then paid else . end), 0) as sumPaid,
    coalesce(sum(case when startdate between start_line1 and stop_line1 then oop else . end), 0) as sumOop
from have
group by pid, start_line1, stop_line1
union all 
select
    pid,
    start_line2 as start_line, stop_line2 as stop_line, 2 as line,
    coalesce(sum(case when startdate between start_line2 and stop_line2 then paid else . end), 0) as sumPaid,
    coalesce(sum(case when startdate between start_line2 and stop_line2 then oop else . end), 0) as sumOop
from have
group by pid, start_line2, stop_line2
order by pid, line;
select * from want;
quit;