DATA Step, Macro, Functions and more

loop queries for multiple calculation

Reply
Occasional Contributor
Posts: 9

loop queries for multiple calculation

          
 Existing DataRequire by loop formula 
 Customer IDAccount NoPrinciple LossInterest LossCredit BalanceRecovery1Recovery2Balance 
 1245969912345678902001001000    
 124596993154645488400250     
 124596995074723086500300     
 124596996994800684750800     
 12459699891487828212040     
 1356224612345578904542102000    
 135622461234567890454455     
 656465461234577890442121500    
 65646546123458789021113     
          
 Existing DataRequire by loop formula 
 Customer IDAccount NoPrinciple LossInterest LossCredit BalanceRecovery1Recovery2Balance 
 1245969912345678902001001000200100700 
 12459699315464548840025070040025050 
 124596995074723086500300505000 
 1245969969948006847508000000 
 124596998914878282120400000 
 13562246123455789045421020004542101336 
 1356224612345678904544551336454455427 
 656465461234577890442121500442121244 
 656465461234587890211131244211131110 
          
Respected Advisor
Posts: 4,927

Re: loop queries for multiple calculation

[ Edited ]

Values only depend on previous obs (assuming proper ordering)

data have;
length CustomerID	AccountNo $12;
infile datalines missover;
input CustomerID	AccountNo	PrincipleLoss	InterestLoss	CreditBalance;
datalines;
 	12459699	1234567890	200	100	1000
 	12459699	3154645488	400	250	 	
 	12459699	5074723086	500	300	 	
 	12459699	6994800684	750	800	 	
 	12459699	8914878282	120	40	 	
 	13562246	1234557890	454	210	2000
 	13562246	1234567890	454	455	 	
 	65646546	1234577890	44	212	1500
 	65646546	1234587890	21	113
;

data want;
do until(last.CustomerId);
    set  have; by CustomerId;
    creditBalance = coalesce(CreditBalance, Balance, 0);
    Recovery1 = min(PrincipleLoss, CreditBalance);
    Recovery2 = min(InterestLoss, CreditBalance-Recovery1);
    Balance = max(0, CreditBalance - Recovery1 - Recovery2);
    output;
    end;
run;

proc print data=want; 
var CustomerID AccountNo PrincipleLoss InterestLoss CreditBalance 
    Recovery1 Recovery2 Balance;
by CustomerId; id customerId; 
run;
PG
Super User
Posts: 10,035

Re: loop queries for multiple calculation

data have;
length CustomerID	AccountNo $12;
infile datalines missover;
input CustomerID	AccountNo	PrincipleLoss	InterestLoss	CreditBalance;
datalines;
 	12459699	1234567890	200	100	1000
 	12459699	3154645488	400	250	 	
 	12459699	5074723086	500	300	 	
 	12459699	6994800684	750	800	 	
 	12459699	8914878282	120	40	 	
 	13562246	1234557890	454	210	2000
 	13562246	1234567890	454	455	 	
 	65646546	1234577890	44	212	1500
 	65646546	1234587890	21	113
;
run;
data want;
 set have;
 by CustomerID;
 retain balance;
 if first.CustomerID then balance=CreditBalance;
 CreditBalance=max(0,balance);
 balance=balance-PrincipleLoss;
 Recovery1=ifn(balance gt 0,PrincipleLoss,max(0,balance+PrincipleLoss));
 balance=balance-InterestLoss;
 Recovery2=ifn(balance gt 0,InterestLoss,max(0,balance+InterestLoss));
 balance=max(0,balance);
run;



Ask a Question
Discussion stats
  • 2 replies
  • 222 views
  • 1 like
  • 3 in conversation