topic Re: Iteration through observations in SAS Programming

Iteration through observations

dhallkaran — Tue, 28 Jul 2020 14:06:40 GMT

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!

Re: Iteration through observations

yabwon — Tue, 28 Jul 2020 14:31:46 GMT

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

Re: Iteration through observations

novinosrin — Tue, 28 Jul 2020 14:32:32 GMT


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;

Re: Iteration through observations

mkeintz — Tue, 28 Jul 2020 15:12:56 GMT

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+ ...").

Re: Iteration through observations

Ksharp — Wed, 29 Jul 2020 12:39:00 GMT

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;