BookmarkSubscribeRSS Feed
ramak
Calcite | Level 5
          
 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 
          
2 REPLIES 2
PGStats
Opal | Level 21

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
Ksharp
Super User
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;



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 830 views
  • 1 like
  • 3 in conversation