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:
- Amt is the transactional amount
- Limit amount is the same across serial numbers
- OBal is the Opening Balance
- New_Bal and PostBal are based on the type of transaction. If type='A' then New_Bal=OBal+Amt otherwise New_Bal=OBal-Amt
- For the first transaction PostBal=(Limit+OBal)+Amt and for the remaining transactions PostBal=OBal-Amt
- 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)
- 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