Reset a Cumulative Sum in PROC SQL Based on Cumulative Sum Values.

[MelissaWillKill]

I am working in PROC SQL to obtain a cumulative sum.  My code currently reads:

PROC SQL;

  CREATE TABLE WorkingFile2 AS

  SELECT *,

              (SELECT SUM(B.value)

              FROM WorkingFile1 AS B

              WHERE B.ID = A.ID

              AND MDY(B.MONTH, 1, B.YEAR) <= MDY(A.MONTH, 1, A.YEAR)) AS SUM

	FROM WorkingFile1 AS A
	ORDER BY A.ID, A.YEAR, A.MONTH;

QUIT;

I would like for the cumulative sum to restart when it reaches a positive value.

ID	Year	Month	Value	Sum	Desired Sum
12345	2013	5	-94.32	-94.32	-94.32
12345	2013	6	19.82	-74.5	-74.5
12345	2013	7	55.49	-19.01	-19.01
12345	2013	8	28.73	9.72	9.72
12345	2013	9	-24.6	-14.88	-24.6
12345	2013	10	-36.4	-51.28	-61

How can I obtain the desired sum?  This requires the cumulative sum to reset based upon its current value. 

[PGStats]

SQL is not the proper tool to do sequential operations such as cumulative sums. Use a datastep instead, it will be simpler and much faster.

data have;

input ID  Year    Month   Value;

datalines;

12345   2013    5   -94.32

12345   2013    6   19.82  

12345   2013    7   55.49  

12345   2013    8   28.73  

12345   2013    9   -24.6  

12345   2013    10  -36.4

;

proc sort data=have; by id year month; run;

data want;

set have; by id;

if first.id or sum>0 then sum=0;

sum + value;

run;

proc print data=want noobs; run;

PG