- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have a group of individuals, and I need to sum 90 days out from each particular date, so that I can find whether or not they ever met a certain threshold within a 90 day period.
For example if I have this table:
ID Amount Date
1 2 1/12/15
1 4 1/30/15
1 6 3/2/15
2 8 1/18/15
2 10 1/20/15
2 12 1/28/15
2 14 2/10/15
2 16 2/12/15
2 18 2/25/15
2 20 3/4/15
2 22 4/1/15
2 24 5/22/15
2 26 5/27/15
2 28 6/3/15
2 30 6/6/15
3 32 1/12/15
3 34 6/12/15
I want these results:
ID Amount Date SUM
1 2 1/12/15 12
1 4 1/30/15 10
1 6 3/2/15 6
2 8 1/18/15 120
2 10 1/20/15 112
2 12 1/28/15 102
2 14 2/10/15 90
2 16 2/12/15 76
2 18 2/25/15 84
2 20 3/4/15 90
2 22 4/1/15 130
2 24 5/22/15 108
2 26 5/27/15 84
2 28 6/3/15 58
2 30 6/6/15 30
3 32 1/12/15 32
3 34 6/12/15 34
And if my threshold were 115, Person 2 would have met that requirement but Persons 1 and 3 would not have.
Hopefully this makes sense to everyone. I was thinking of using a DO WHILE loop, but I'm not sure whether or not that would work here. Any help would be greatly appreciated.
Thanks!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Code: Program
data have;
input ID Amount Date : mmddyy10.;
format Date mmddyy10.;
cards;
1 2 1/12/15
1 4 1/30/15
1 6 3/2/15
2 8 1/18/15
2 10 1/20/15
2 12 1/28/15
2 14 2/10/15
2 16 2/12/15
2 18 2/25/15
2 20 3/4/15
2 22 4/1/15
2 24 5/22/15
2 26 5/27/15
2 28 6/3/15
2 30 6/6/15
3 32 1/12/15
3 34 6/12/15
;
run;
proc sql;
create table want as
select *,(select sum(Amount) from have where id=a.id and Date between a.Date and a.Date+90) as sum
from have as a ;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Code: Program
data have;
input ID Amount Date : mmddyy10.;
format Date mmddyy10.;
cards;
1 2 1/12/15
1 4 1/30/15
1 6 3/2/15
2 8 1/18/15
2 10 1/20/15
2 12 1/28/15
2 14 2/10/15
2 16 2/12/15
2 18 2/25/15
2 20 3/4/15
2 22 4/1/15
2 24 5/22/15
2 26 5/27/15
2 28 6/3/15
2 30 6/6/15
3 32 1/12/15
3 34 6/12/15
;
run;
proc sql;
create table want as
select *,(select sum(Amount) from have where id=a.id and Date between a.Date and a.Date+90) as sum
from have as a ;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That worked perfectly, thank you!