Hi community members,
I have a data set that has credit card transactions for accounts through time.
Have | Have | Have | Required | Required |
Account | Credit _Limit | Transaction_amount | Decision | OTB_remaining |
1 | 1000 | 500 | Approve | 500 |
1 | 1000 | 900 | Decline | 500 |
1 | 1000 | 400 | Approve | 100 |
2 | 2000 | 1000 | Approve | 1000 |
2 | 2000 | 1500 | Decline | 1000 |
2 | 2000 | 2000 | Decline | 1000 |
2 | 2000 | 1000 | Approve | 0 |
Have are the variables that are existing in the data set.
Required are the variables that I need to create.
Example Account#1:
1. As the first transaction for account#1 is for $500 and the credit limit is $1000, the transaction should be approved and the Open To Buy(OTB) should be Credit Limit ($1000)-Transaction Amount($500)=$500.
2. Now for next transaction as $500 is available for spend, and the customer tries a spend of $900, the transaction should be declined and $500 should be OTB available for next transaction.
3. As the 3rd transaction is for $400 and $500 is available to spend, the transaction should be approved and OTB should be updated to $100.
The same should apply to Account#2.
As this requires holding previous values of OTB and decision to make subsequent decisions, I am having a hard time figuring out how to code it in SAS.
Any help here is much appreciated!
Hi,
This seems to be doing the job:
data have;
input Account Credit_Limit Transaction_amount; /* Decision OTB_remaining */
cards;
1 1000 500 Approve 500
1 1000 900 Decline 500
1 1000 400 Approve 100
2 2000 1000 Approve 1000
2 2000 1500 Decline 1000
2 2000 2000 Decline 1000
2 2000 1000 Approve 0
;
run;
data want;
set have;
by Account;
/* current limit on the account */
retain current_CL 0;
if first.account then current_CL = Credit_Limit;
drop current_CL ;
/* test for credit limit */
if Transaction_amount <= current_CL then
do;
Decision = "Approve";
OTB_remaining = current_CL - Transaction_amount;
end;
else
do;
Decision = "Decline";
OTB_remaining = current_CL;
end;
output;
current_CL = OTB_remaining;
run;
proc print;
run;
Bart
data have;
input Account Credit_Limit Transaction_amount;* Decision OTB_remaining;
cards;
1 1000 500 Approve 500
1 1000 900 Decline 500
1 1000 400 Approve 100
2 2000 1000 Approve 1000
2 2000 1500 Decline 1000
2 2000 2000 Decline 1000
2 2000 1000 Approve 0
;
data want;
do until(last.account);
set have;
by account;
if first.account then OTB_remaining=Credit_Limit;
_n_=OTB_remaining-Transaction_amount;
if sign(_n_)=-1 then Decision='Decline';
else do;
Decision='Approve';
OTB_remaining=_n_;
end;
output;
end;
run;
I think @yabwon 's response clarifies the logic nicely. This program is a little different - instead of checking a condition and then making two modifications (decision and otb_remaining), this checks the condition and then makes one modification (decision). Then it uses decision to conditionally make the second modification.
data have;
input Account Credit_Limit Transaction_amount; /* Decision OTB_remaining */
cards;
1 1000 500 Approve 500
1 1000 900 Decline 500
1 1000 400 Approve 100
2 2000 1000 Approve 1000
2 2000 1500 Decline 1000
2 2000 2000 Decline 1000
2 2000 1000 Approve 0
;
run;
data want;
set have;
by account;
length decision $8;
if first.account then otb_remaining=credit_limit;
decision=ifc(transaction_amount<=otb_remaining,'accept','decline');
if decision='accept' then otb_remaining+ -1*transaction_amount;
run;
There's also a non-obvious "summing statement" above (a summing statement is like "x+a;" instead of "x=x+a;"). When a variable is the outcome of a summing statement, it is automatically retained.
The summing statement here is conditionally executed in the "if decision='accept' test. And notice I had to change its syntax to
otb_remaining+ -1*transaction_amount;
since I needed to subtract, not add - while preserving the recognizable syntax of a summing statement (must start with "X+ ...").
data have; input Account Credit_Limit Transaction_amount; cards; 1 1000 500 Approve 500 1 1000 900 Decline 500 1 1000 400 Approve 100 2 2000 1000 Approve 1000 2 2000 1500 Decline 1000 2 2000 2000 Decline 1000 2 2000 1000 Approve 0 ; run; data want; set have; by Account; if first.Account then cum=0; cum+Transaction_amount; _Transaction_amoun=Transaction_amount; if cum<=Credit_Limit then Decision='Approve '; else do; cum=cum-_Transaction_amoun; Decision='Decline '; end; OTB_remaining=Credit_Limit-cum; drop _Transaction_amoun cum; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.