topic loop queries for multiple calculation in SAS Programming

loop queries for multiple calculation

ramak — Fri, 05 Aug 2016 15:27:44 GMT


Existing Data	Require by loop formula
Customer ID	Account No	Principle Loss	Interest Loss	Credit Balance	Recovery1	Recovery2	Balance
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

Existing Data	Require by loop formula
Customer ID	Account No	Principle Loss	Interest Loss	Credit Balance	Recovery1	Recovery2	Balance
12459699	1234567890	200	100	1000	200	100	700
12459699	3154645488	400	250	700	400	250	50
12459699	5074723086	500	300	50	50	0	0
12459699	6994800684	750	800	0	0	0	0
12459699	8914878282	120	40	0	0	0	0
13562246	1234557890	454	210	2000	454	210	1336
13562246	1234567890	454	455	1336	454	455	427
65646546	1234577890	44	212	1500	44	212	1244
65646546	1234587890	21	113	1244	21	113	1110

Re: loop queries for multiple calculation

PGStats — Fri, 05 Aug 2016 22:05:43 GMT

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;

Re: loop queries for multiple calculation

Ksharp — Sat, 06 Aug 2016 07:02:18 GMT

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;