Logic Help Required

[bnarang]

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

[Patrick · Accepted solution]

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;

[bnarang]

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

[Patrick]

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"