topic Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE in SAS Programming

HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

rohithverma — Sun, 20 Jun 2021 17:07:11 GMT

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..!!

Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

Sajid01 — Sun, 20 Jun 2021 15:41:25 GMT

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

Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

rohithverma — Sun, 20 Jun 2021 17:06:32 GMT

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

Re: HOW TO CHECK PREVIOUS ROW IS POSITIVE OR NEGATIVE

Quentin — Sun, 20 Jun 2021 17:22:58 GMT

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 ;