HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

[rohithverma]

I had a input like below .Suppose if todays date is june5th2021 and the amount is -500 . I need to check the previous day balance  and if the previous days balance(4thjune2021) is also a negative value then again i need to check the before previous day balance(3thjune2021) till the positive balance value is reflected .

Finally the total should be sum of all previous days negative balance till the positive value appears  reflected as like in the below table marked in RED .

particulars	amount
Amt to be received' as on 01 JUN 2021	-300
Amt  to be received' as on 02 JUN 2021	200
Amt to be received' as on 03 JUN 2021	-300
Amt to be received' as on 04 JUN 2021	-400
Amt to be received' as on 05 JUN 2021	-500
Total	1200

Please help me .Thanks in advance..!! 

[Sajid01]

This example code with the data you have provided, helps do what you wanted.

data test;
length particulars $ 50;
retain sm;
input particulars $ 	amount;
infiles datalines dsd dlm=',' missover;
if amount <0 then status='Y';
datalines;
Amt to be received' as on 01 JUN 2021,	0
Amt  to be received' as on 02 JUN 2021,	200
Amt to be received' as on 03 JUN 2021,	-300
Amt to be received' as on 04 JUN 2021,	-400
Amt to be received' as on 05 JUN 2021,	-500
;
run;
proc sql;
create table temp as
select "Total" as Particulars Length= $ 50, sum(amount)*(-1) as amount from test 
where status='Y';
quit;
proc append base=test(drop=status) data=temp Force;
run;
data test (drop=Status);
set test;
run;
proc print;
run;

The output will be like this

[rohithverma]

Thanks for your reply .But as per UPDATED requirement shown below table the sum should be still 1200

particulars amount
Amt to be received' as on 01 JUN 2021 -300
Amt to be received' as on 02 JUN 2021 200
Amt to be received' as on 03 JUN 2021 -300
Amt to be received' as on 04 JUN 2021 -400
Amt to be received' as on 05 JUN 2021 -500
Total 1200

[Quentin · Accepted solution]

Hi,

With a DATA step, calculating that sort of running total is straight forward.  You need to use the RETAIN statement  to retain the value of accumulator variable.  The SUM statement implicitly retains an accumulator as well.

data have ;
  input amount ;
  cards ;
-300
200
-300
-400
-500 
;
run ;

data want ;
  set have ;

  if amount<0 then Total+abs(Amount) ; *Total is an accumulator variable;
  else Total=0 ; *Reset total to 0 whenever Amount is >0;

  put (_n_ amount total)(=) ;
run ;