Summing by ID and date

[PetePatel]

Hi,

I have a dataset in a similar format to below:

ID	Date	Amount
1	20-Oct-17	-50
1	20-Oct-17	60
1	21-Oct-17	100
1	11-Nov-17	250
2	01-Jul-18	50
2	02-Jul-18	-100
2	02-Jul-18	100
2	02-Jul-18	-50
2	05-Jul-18	200

I would like to sum by ID and date, and where negative default to 0 as follows:

ID	Date	New_amount
1	20-Oct-17	0
1	20-Oct-17	10
2	01-Jul-18	50
2	02-Jul-18	0
2	05-Jul-18	200

How can I easily create 'New_Amount' as I am grouping by 2 variables?

Thanks

[Kurt_Bremser · Accepted solution]

Simple in SQL:

proc sql;
create table want as
select id, date, max(sum(amount),0) as new_amount
from have
group by id, date;
quit;