Have:
ID | Date | Cases |
1 | 01Mar2000 | 100 |
1 | 05Mar2000 | 150 |
1 | 20Mar2000 | 100 |
1 | 04Apr2000 | 500 |
1 | 08Jun2000 | 300 |
Want:
ID | Date | Cases | Date_new | Sum_cases |
1 | 01Mar2000 | 100 | 20Mar2000 | 350 |
1 | 05Mar2000 | 150 | 04Apr2000 | 750 |
1 | 20Mar2000 | 100 | 04Apr2000 | 600 |
1 | 04Apr2000 | 500 | 04Apr2000 | 500 |
1 | 08Jun2000 | 300 | 08Jun2000 | 300 |
‘Date_new’ is up to a maximum of 30 days from date and ‘sum_cases’ is summing all ‘cases’ up to date_new.
Note: It has to work within ID (i.e. treat ID as group).
In my example above, in the first row date_new=20Mar2000 because 20Mar2000-01Mar2000<30 days while 04Apr2000-01Mar>30 days.
/*
If you don't have a big table,could try this one.
*/
data have;
input ID
Date :date9.
Cases;
format date date9.;
cards;
1
01Mar2000
100
1
05Mar2000
150
1
20Mar2000
100
1
04Apr2000
500
1
08Jun2000
300
;
proc sql;
create table want as
select *,(select sum(Cases) from have where id=a.id and date between a.date and a.date+30) as Sum_cases
from have as a;
quit;
/*
If you don't have a big table,could try this one.
*/
data have;
input ID
Date :date9.
Cases;
format date date9.;
cards;
1
01Mar2000
100
1
05Mar2000
150
1
20Mar2000
100
1
04Apr2000
500
1
08Jun2000
300
;
proc sql;
create table want as
select *,(select sum(Cases) from have where id=a.id and date between a.date and a.date+30) as Sum_cases
from have as a;
quit;
Thank you, it seems that it is working.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.