topic Re: get value until get positive value group wise in SAS Programming

get value until get positive value group wise

thanikondharish — Mon, 25 Jan 2021 12:43:43 GMT

amt new_amt
2 2
-3 0
-4 0
5 5(-7)=-2 so should come zero and (-2 remaining value)
6 -2+6=4 (value 4 should come)(until value get positive)
7 7
-4 0
2 -4+2=-2 (negative so value should come 0)

finally

amt new_amt
2 2
-3 0
-4 0
5 0
6 4
7 7
-4 0
2 0

we have one data like negative and positive values are there if negative value is there then we have to subtract next value until get positive. I have give raw data and final data

Re: get value until get positive value group wise

PeterClemmensen — Mon, 25 Jan 2021 13:07:14 GMT

Do something like this

data have;
input amt;
datalines; 
2    
-3   
-4   
5    
6    
7    
-4   
2    
;

data want(drop = n);
   set have;
   new_amt = amt;
   if new_amt < 0 then do;
      n + new_amt;
      new_amt = 0;
   end;
   if new_amt > 0 & n < 0 then do;
      new_amt = max(0, new_amt + n);
      n = min(n + amt, 0);
   end;
run;

Result:

amt  new_amt 
2    2 
-3   0 
-4   0 
5    0 
6    4 
7    7 
-4   0 
2    0