## Logic Help Required

Solved
Frequent Contributor
Posts: 86

# Logic Help Required

Hi

I have a business problem and I am stuck and not able to drive the solution. Anyone able to help or provide some hints would be appreciated.

Consider the hypothetical example below:

 Shop Cust_bal1 Cust_bal2 Cust_bal3 Payments 1 30 40 50 20 1 30 40 50 25 1 30 40 50 30 1 30 40 50 40 1 30 40 50 10 1 30 40 50 15

In a particular shop, A consumer balance is segmented into three Bal1 bal2 and bal3. Bal1 being the highest priority, Bal2 and Bal3. This is retained across the observation and now the consider the payments are made. The Balances has to be moved in such a way that bal1 should be given highest priority then bal2 and so on.

Ideally, the output should be like:

 Shop Cust_bal1 Cust_bal2 Cust_bal3 Payments cal_bal1 Cal_bal2 Cal_bal3 Payments_1 1 30 40 50 20 10 40 50 20 1 30 40 50 25 0 25 50 10 1 30 40 50 30 0 0 45 25 1 30 40 50 40 0 0 5 40 1 30 40 50 5 0 0 0 5 1 30 40 50 0 0 0 0 0

In the end, I would only consider the Payments which were contributed for Bal1 and Bal3 and created a separate column payments_1. I have manually created the example and output in Excel because I could not apply the logic in SAS.

Thank you so much for help.

Thanks

Accepted Solutions
Solution
‎10-27-2013 02:22 AM
Posts: 4,736

## Re: Logic Help Required

data have;
Payment_Order=_n_;
Customer=1;
input Shop Cust_bal1 Cust_bal2 Cust_bal3 Payments;
datalines;
1 30 40 50 20
1 30 40 50 25
1 30 40 50 30
1 30 40 50 40
1 30 40 50 10
1 30 40 50 15
;
run;

data want(drop=_;
set have;
by Shop Customer Payment_Order;

retain Cal_bal1 Cal_bal2 Cal_bal3 Payments_1 0;

array Cal_Bal {*} 8. Cal_bal1 Cal_bal2 Cal_bal3;
array Cust_bal {*}   Cust_bal1 Cust_bal2 Cust_bal3;

/* initiate Cal_Bal vars for a new customer */
if first.Customer then
do;
do _i=1 to dim(Cal_Bal);
Cal_Bal[_i]=Cust_bal[_i];
end;
_start=1;
end;

_remainder=Payments;
_Balance1=sum(cal_bal1,cal_bal3);
do _i=_start to dim(Cal_Bal);
Cal_Bal[_i]=Cal_Bal[_i]-_remainder;
if Cal_Bal[_i]<=0 then
do;
_remainder=abs(Cal_Bal[_i]);
Cal_Bal[_i]=0;
_start+1;
end;
else leave;
end;
Payments_1=_Balance1-sum(cal_bal1,cal_bal3);
run;

All Replies
Solution
‎10-27-2013 02:22 AM
Posts: 4,736

## Re: Logic Help Required

data have;
Payment_Order=_n_;
Customer=1;
input Shop Cust_bal1 Cust_bal2 Cust_bal3 Payments;
datalines;
1 30 40 50 20
1 30 40 50 25
1 30 40 50 30
1 30 40 50 40
1 30 40 50 10
1 30 40 50 15
;
run;

data want(drop=_;
set have;
by Shop Customer Payment_Order;

retain Cal_bal1 Cal_bal2 Cal_bal3 Payments_1 0;

array Cal_Bal {*} 8. Cal_bal1 Cal_bal2 Cal_bal3;
array Cust_bal {*}   Cust_bal1 Cust_bal2 Cust_bal3;

/* initiate Cal_Bal vars for a new customer */
if first.Customer then
do;
do _i=1 to dim(Cal_Bal);
Cal_Bal[_i]=Cust_bal[_i];
end;
_start=1;
end;

_remainder=Payments;
_Balance1=sum(cal_bal1,cal_bal3);
do _i=_start to dim(Cal_Bal);
Cal_Bal[_i]=Cal_Bal[_i]-_remainder;
if Cal_Bal[_i]<=0 then
do;
_remainder=abs(Cal_Bal[_i]);
Cal_Bal[_i]=0;
_start+1;
end;
else leave;
end;
Payments_1=_Balance1-sum(cal_bal1,cal_bal3);
run;

Frequent Contributor
Posts: 86

## Re: Logic Help Required

Thank you so much.

The code worked pretty well and I had no difficulty. Overall, I have understood what you tried to do, But would you please explain the Balance_1 part because I thought this is calculated before the loop starts but it is calculated dynamically which I could not understand. Please expalin.

Thanks Again

Posts: 4,736

## Re: Logic Help Required

May be I shouldn't have called it "Balance_1". What it does: I get the sum of cal_bal1 and cal_bal3 BEFORE deducting the new payments (that's done in the loop).

After the new payment has been deducted I simply substract the sum of the new amounts of cal_bal1 and cal_bal3 from "Balance_1". The difference is what you want in "Payments_1".

Payments_1="sum(cal_bal1,cal_bal3) before latest deduction of payment" minus "sum(cal_bal1,cal_bal3) after deduction of latest payment"

🔒 This topic is solved and locked.