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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.