vendredi 28 juin 2019

Calculations from multiple variables using SAS

situation

I have transactional data and I need to create a couple of variables in the dataset involving some complicated rules.

Here are the rules that I have:

  1. Amt is the transactional amount
  2. Limit amount is the same across serial numbers
  3. OBal is the Opening Balance
  4. New_Bal and PostBal are based on the type of transaction. If type='A' then New_Bal=OBal+Amt otherwise New_Bal=OBal-Amt
  5. For the first transaction PostBal=(Limit+OBal)+Amt and for the remaining transactions PostBal=OBal-Amt
  6. fee and fee_amt are to be created if the type='B' and Amt>5
    • If the Amt is greater than PostBal then we will keep the same New_Bal and Post_Bal and add fee=1, fee_amt=10 and fee_ind=N (Row 4 for id 101 from the desired output)
    • If the Amt is less than PostBal then we will add Post_Bal and fee=2, fee_amt=10 and fee_ind=Y (Row 5 for id 101 from the desired output)
  7. We will limit fee to 3 and subtract that amount from final transaction amount (Row 6 for id 101 -22-30=-52)

Here is the example of data for two different ids and serial numbers:

Initial Data

   id serial   type Amt    OBal    Limit
   101  1234     A    100    -50     100
   101  1234     B     25               
   101  1234     B     50               
   101  1234     B     80               
   101  1234     B     50               
   101  1234     B      3               
   101  5678     A    100            100 
   201  1234     A    100    -50     100
   201  1234     B     25               
   201  1234     B     50               
   201  1234     B     50               
   201  1234     B     50               
   201  1234     B     20                
   201  5678     B     50           100 

Code I have tried:

data want;
    set have;
    by id serial;

    /*Calculate OBal*/
    if first.serial and not first.id then OBal  =  New_Bal;
    if first.serial then do;
        New_Bal  =  OBal;
        PostBal  =  Limit + NewBal;
        fee  =  0;
    end;
    if type = 'A' then NewBal + AMt;
    if type = 'B' then NewBal + (-AMt);

    if type = 'A' then PostBal + AMt;
    if type = 'B' then PostBal + (-AMt);
run;

My code is creating NewBal and PostBal properly. However, I am unable to loop them to add the fee and fee_ind. This is a very complicated task for me and I know this group has some great talent who can help me in resolving this.

Thanks in advance

Desired result:

      id   serial   type Amt    OBal    Limit   New_Bal  PostBal    fee    fee_amt  fee_ind
       101  1234     A    100    -50     100      50       150
       101  1234     B     25                     75       125        1      10         Y
       101  1234     B     50                     25        75        2      10         Y 
       101  1234     B     80                     25        75                0         N
       101  1234     B     50                    -25       -25        3      10         Y
       101  1234     B     3                     -52       -28                          
       101  5678     A    100    -52     100      48       148 
       201  1234     A    100    -50     100      50       150
       201  1234     B     25                     75       125        1      10         Y
       201  1234     B     50                     25        75        2      10         Y 
       201  1234     B     50                    -25        25        3      10         Y
       201  1234     B     50                    -25        25                0         N
       201  1234     B     20                    -55        25                0         N
       201  5678     B     50    -55     100       5        -5

Aucun commentaire:

Enregistrer un commentaire