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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.