SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
klaw12
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

2 REPLIES 2
Ksharp
Super User

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;
klaw12
Calcite | Level 5

That worked perfectly, thank you!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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