topic Re: Summarise consecutive records on condition in SAS Programming

Summarise consecutive records on condition

Natalie111 — Fri, 06 Oct 2023 11:50:01 GMT

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.

Re: Summarise consecutive records on condition

Ksharp — Fri, 06 Oct 2023 13:03:59 GMT

/*
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;

Re: Summarise consecutive records on condition

Natalie111 — Tue, 10 Oct 2023 07:24:11 GMT

Thank you, it seems that it is working.