DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 1

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

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.

IDYearMonthValueSumDesired Sum
1234520135

-94.32

-94.32-94.32
123452013619.82-74.5-74.5
123452013755.49-19.01-19.01
123452013828.739.729.72
1234520139-24.6-14.88-24.6
12345201310-36.4-51.28-61

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

Respected Advisor
Posts: 4,651

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

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

PG
Ask a Question
Discussion stats
  • 1 reply
  • 1510 views
  • 2 likes
  • 2 in conversation