DATA Step, Macro, Functions and more

Logic Help Required

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

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:

ShopCust_bal1Cust_bal2Cust_bal3Payments
130405020
130405025
130405030
130405040
130405010
130405015

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:

ShopCust_bal1Cust_bal2Cust_bal3Paymentscal_bal1Cal_bal2Cal_bal3Payments_1
13040502010405020
1304050250255010
130405030004525
13040504000540
130405050005
130405000000

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
Respected Advisor
Posts: 3,896

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=_Smiley Happy;
  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;

View solution in original post


All Replies
Solution
‎10-27-2013 02:22 AM
Respected Advisor
Posts: 3,896

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=_Smiley Happy;
  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

Respected Advisor
Posts: 3,896

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 195 views
  • 4 likes
  • 2 in conversation