Hello Everyone:
Having issues with developing logic around rewarding customers who open new deposit accounts on the same day vs different days. Only one reward is provided per newly opened account.
Scenario........
Have:
If customer A and customer B open a jointly held deposit account on Day 1 and
customer B and customer C open a joint account on Day 1 and
customer A and customer C open a joint account on Day 1 then reward customers A, B, C (3 rewards paid out)
If customer D and customer E open a joint account: reward either customer D or E (1 reward paid out)
If customer I and customer J and customer K open a joint account: reward either customer I or J or K (1 reward paid out)
However;
If customer F and customer G open a jointly held deposit account on Day 1 and
customer G and customer H open a joint account on Day 2 and
customer F and customer H open a joint account on Day 3 then reward customers F or G. (1 reward paid out)
(spaces added to datalines below to help clarify above groups)
data have;
infile datalines truncover;
input customer_no $ acctno $ open_date;
datalines;
A 999 20221201
B 999 20221201
B 888 20221201
C 888 20221201
A 777 20221201
C 777 20221201
D 666 20221201
E 666 20221201
F 555 20221205
G 555 20221205
G 444 20221206
H 444 20221206
F 333 20221207
H 333 20221207
I 222 20221209
J 222 20221209
K 222 20221209
;
proc print data=have;
title 'have';
run;
proc sort data=have out=temp2;
by customer_no open_date;
run;
proc print data=temp2(obs=40);
title 'temp2';
run;
data temp3;
set temp2;
by customer_no open_date;
if first.customer_no;
run;
proc print data=temp3(obs=40);
title 'temp3';
run;
Want:
A 999 20221201
B 888 20221201
C 777 20221201
D 666 20221201
F 555 20221205
I 222 20221209
So in the proc print table for dataset 'temp3'; below customers A,B,C,D,F, I are fine. However, I do not want customers E,G,H,J,K. I'm stuck at this point and at a loss how to eliminate those records.
data have; infile datalines truncover; input customer_no $ acctno $ open_date; datalines; A 999 20221201 B 999 20221201 B 888 20221201 C 888 20221201 A 777 20221201 C 777 20221201 D 666 20221201 E 666 20221201 F 555 20221205 G 555 20221205 G 444 20221206 H 444 20221206 F 333 20221207 H 333 20221207 I 222 20221209 J 222 20221209 K 222 20221209 ; proc print data=have; title 'have'; run; proc sort data=have out=temp2; by customer_no open_date; run; proc print data=temp2(obs=40); title 'temp2'; run; data temp3; set temp2; by customer_no open_date; if first.customer_no; run; proc print data=temp3(obs=40); title 'temp3'; run; /* WANT A 999 20221201 B 888 20221201 C 777 20221201 D 666 20221201 F 555 20221205 I 222 20221209 */
Many thanks for your assistance!
I think I would approach this with a hash table. I don't have time to try coding it, but I think would approach like:
I think that might do it.
Edit: I don't think the above handles a case where A opens two accounts on the same day. It would give them double rewards. Perhaps that could be de-duped in a separate step. What happens if A opens an account on day 1 and also on day 1 A and B open an account. Does B get a reward? Above algorithm would say this account generates an award. But would need another step to determine that the award goes to B. Perhaps the above step would be useful for generating all the accounts that trigger rewards. Then a separate step could be used to dedup and determine who gets the reward from each account. This is tricky. : )
Hi,
I don't understand the last group:
If customer F and customer G open a jointly held deposit account on Day 1 and
customer G and customer H open a joint account on Day 2 and
customer F and customer H open a joint account on Day 3 then reward customers F or G. (1 reward paid out)
So if these three accounts were all opened on the same day, you would pay out 3 rewards (which is the first group). But when they are opened on different days, you only pay 1 reward? What happens if there is a gap in dates, e.g. F and G open a join account on Jan 1, then G and H open a join account on Jun 1?
I can understand a rule "each account only triggers one reward, and no customer gets two rewards." But the date part is hurting my head.
@sasasauraus wrote:
Yes this logic hurts my head too. In any respect; the campaign involves (new) FIRST deposit accounts for the customers. If the account is opened on Jan 1st by customers F & G (their first new account) then any subsequent account openings on later days or weeks or months are disqualified. Only if multiple accounts are opened on the SAME day do they (accounts) qualify. In your example above; the account opened by G and H on Jun 1 is disqualified because it is not their first newly opened account (their first new account was opened previously on Jan 1).
What if for a joint account customer A already got an earlier account opening but customer B is new? Does that disqualify the account?
Your WANT data includes the customer. For a joint account that qualifies: Any logic which customer gets the reward?
If a customer opens two accounts on the same day (FIRST deposit) you state that both accounts qualify: Does this mean the customer gets two rewards?
What if for a joint account customer A already got an earlier account opening but customer B is new? Does that disqualify the account? Correct. The latter account no longer qualifies as it is now the 2nd opened account.
Your WANT data includes the customer. For a joint account that qualifies: Any logic which customer gets the reward?
Only one payout per opened account will qualify regardless of how many joint holders are on that particular account. Does not matter who gets the reward per account. Customers should not be rewarded multiple times.
If a customer opens two accounts on the same day (FIRST deposit) you state that both accounts qualify: Does this mean the customer gets two rewards? No; this is considered a single held account not joint. If A opens acct #111 and A opens acct# 222 on the same day then A is rewarded once.
Adding notes to help clarify matters further:
/* WANT */
A 999 20221201
B 888 20221201
C 777 20221201
D or E 666 20221201
F or G 555 20221205
I or J or K 222 20221209
Notes as per above WANT:
Acct# 555 (F or G qualifies as this is their 1st account - but only 1 customer will be rewarded)
Acct# 666 (D or E qualifies as this is their 1st account - but only 1 customer will be rewarded)
Acct# 222 (I or J or K qualifies as this is their 1st account - but only 1 customer will be rewarded)
*****************************************************
F 555 20221205
G 555 20221205
G 444 20221206 (disqualified as 1st account opened 2022-12-05)
H 444 20221206 (disqualified as joint customer G's 1st account opened 2022-12-05)
F 333 20221207 (disqualified as 1st account opened 2022-12-05)
H 333 20221207 (disqualified as joint customer F's 1st account opened 2022-12-05)
I'm still struggling with defining the rule. Is it:
A new account triggers a reward (for one of the account owners) unless one of the account owners for the new account is also an owner of an account the previously triggered a reward. (Previously means an account opened on an earlier date).
?
Yes, that is correct. Essentially; if customer(s) open any subsequent account(s) any time afterwards, then these records can be tossed out. Only the very first open accounts are in consideration for reward. My earlier example:
Customer Account Open_Date
------------ ---------- --------------
F 555 20221205 (F or G qualifies as this is their 1st account - reward F or G but not both)
G 555 20221205 (F or G qualifies as this is their 1st account - reward F or G but not both)
G 444 20221206 (does not qualify as G opened a previous acct on 2022105)
H 444 20221206 (does not qualify as joint owner G opened a previous acct on 2022105)
F 333 20221207 (does not qualify as F opened a previous acct on 2022105)
H 333 20221207 (does not qualify as H opened a previous acct on 2022106 with G who opened a previous account on 20221205)
I think I would approach this with a hash table. I don't have time to try coding it, but I think would approach like:
I think that might do it.
Edit: I don't think the above handles a case where A opens two accounts on the same day. It would give them double rewards. Perhaps that could be de-duped in a separate step. What happens if A opens an account on day 1 and also on day 1 A and B open an account. Does B get a reward? Above algorithm would say this account generates an award. But would need another step to determine that the award goes to B. Perhaps the above step would be useful for generating all the accounts that trigger rewards. Then a separate step could be used to dedup and determine who gets the reward from each account. This is tricky. : )
Thanks Quentin. I do not have familiarity with hash tables as I am a novice SAS user. Nevertheless; I will give your advice a shot. Also; to answer your question....yes, B would also get a reward as we have two accounts opened on the same day. One reward per account opened on the same day but do not reward the same customer twice.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: