BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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"

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
  • 3 replies
  • 768 views
  • 4 likes
  • 2 in conversation