sum of Z between date 1 and date2:multiple rows per ID

Ronein — Mon, 22 Mar 2021 16:34:47 GMT

Hello,

Continue from the previous question, now there can be multiple rows for each customer in data set ttt1.

Which solution will work now?

Remind that the task is to add a new column to data set "ttt1" that is called "Sum_Z_date1_till_date2".

In this new column need to calculate sum of Z between date 1 and date 2.

Expected results is :

for ID=1 01MAR2021 05MAR2021 : 50

for ID=1 27FEB2021 28FEB2021: 10

for ID=2 08MAR2021 15MAR2021L 120

What do you think about the following solution?

will it work well?

Wil it be usefull code?(less time of running)

It is based on many to many merge using Cartesian product (creates all possible combinations of matching observations)

data ttt1;
format date1 date2 date9.;
input ID date1 :date9. date2 :date9.;
cards;
1 01MAR2021 05MAR2021
2 08MAR2021 15MAR2021
1 27FEB2021 28FEB2021
;
Run;

data ttt2;
input ID date3 :date9. Z;
cards;
1 28FEB2021 10
1 02MAR2021 20
1 04MAR2021 30
1 06MAR2021 10
2 08MAR2021 50
2 14MAR2021 70
2 18MAR2021 80
;
Run;

/*Cartesian product:Many to many merge*/
proc sql;
create table ttt3 as
select *
from ttt1 a
join ttt2 b
on a.ID=b.ID;
quit;

Data ttt4;
set ttt3;
Indicator=(Date1<=Date3<=Date2);
Run;

proc sql;
create table ttt5 as
select ID,date1,date2,
sum(z*Indicator)
from ttt3
group by Id,Date1,Date2;
quit;

proc sort data=ttt1;by id date1 date2;run;
proc sort data=ttt5;by id date1 date2;run;
Data wanted;
merge ttt1(in=a) ttt5(in=b);
By id date1 date2;
IF a;
Run;

Re: sum of Z between date 1 and date2:multiple rows per ID

PaigeMiller — Mon, 22 Mar 2021 17:38:54 GMT

I think my solution in the other thread works in this case. Please try it.

topic Re: sum of Z between date 1 and date2:multiple rows per ID in SAS Programming

sum of Z between date 1 and date2:multiple rows per ID

Re: sum of Z between date 1 and date2:multiple rows per ID