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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: