Let us know if following is what you are looking for:
data data1;
infile cards dsd;
input Productcode$ level rate;
cards;
123,10,10
123,50,20
123,100,30
123,999999,50
;
data data2;
infile cards dsd;
input contract Productcode$ balance;
cards;
1,123,75
2,123,12
3,123,175
;
run;
proc sql noprint;
create table want as
select a.*,b.level, b.rate
from data2 as a left join data1 as b
on a.Productcode=b.Productcode
order by a.Productcode, a.contract, b.level;
quit;
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;
... View more