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

MelissaWillKill — Wed, 05 Nov 2014 01:25:01 GMT

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.

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

PGStats — Wed, 05 Nov 2014 03:06:16 GMT

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;