Thank you all,   I guess working on the way ndp recommended will lead to success.   I test it and identify one issue with the code:   data want; set want; by Productcode contract level; retain diff; if first.contract and balance-level>=0 then do; balbylevel=level; diff=balance-level; end; else if first.contract and balance-level<0 then do; balbylevel=balance; diff=0; end; else do; if diff=0 then balbylevel=0; else if diff>level then do; balbylevel=level-(balance-diff); diff=balance-level; end; else do; balbylevel=diff; diff=0; end; end; run; line  else if diff>level then do; balbylevel=level-(balance-diff); diff=balance-level; end; leads to wrong results if you only compare the current diff value with the current level value. you have to compare the current diff value with the (current level value - the previous level value).       So i think i need another retaining variable I call "prelevel" for the last lavel value to substract from the actual level value for that check which leads to 100% correct results for every of my contracts:    data want; set want; by Productcode contract level; retain diff; retain prelevel; if first.contract and balance-level>=0 then do; balbylevel=level; diff=balance-level; prelevel=level; end; else if first.contract and balance-level<0 then do; balbylevel=balance; diff=0; end; else do; if diff=0 then balbylevel=0; else if diff>(level-prelevel) then do; balbylevel=level-(balance-diff); diff=balance-level; prelevel=level; end; else do; balbylevel=diff; diff=0; end; end; run;   At least a simple summary query shows the total volume at each interest level of the product making calculating the avarage interest rate also very easy.    Thank you    ... View more