Conditional Loop within Group

klaw12 — Thu, 11 Jun 2015 18:16:19 GMT

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!

Re: Conditional Loop within Group

Ksharp — Fri, 12 Jun 2015 14:49:43 GMT

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;

Re: Conditional Loop within Group

klaw12 — Fri, 12 Jun 2015 22:33:22 GMT

That worked perfectly, thank you!

topic Conditional Loop within Group in SAS Programming

Conditional Loop within Group

Re: Conditional Loop within Group

Code: Program

Re: Conditional Loop within Group