DATA Step, Macro, Functions and more

Conditional Loop within Group

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Conditional Loop within Group

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
Solution
‎06-12-2015 10:49 AM
Super User
Posts: 9,662

Re: Conditional Loop within Group

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;

View solution in original post


All Replies
Solution
‎06-12-2015 10:49 AM
Super User
Posts: 9,662

Re: Conditional Loop within Group

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;
New Contributor
Posts: 2

Re: Conditional Loop within Group

That worked perfectly, thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 228 views
  • 0 likes
  • 2 in conversation