Credit Card Project [6-6]

The final step of the promotion is to give customers a reward after they have met the promotion criteria.

The promotion criteria are as follows:

  • If the customer has spent between $3000 to $4000, they will receive a 3% rebate.
  • If the customer has spent more than $4000, they will receive a 5% rebate.
  • The maximum rebate amount is $200.

Example 1: Tom has spent $3500 in February. He is eligible to receive ($3500 - $3000) * 0.03 = $15.

Example 2: Susan has spent $15000 in February. She is eligible to receive ($15000 - $4000) * 0.03 = $550. However, the maximum rebate applies and she will only receive $200.

Below is the code to calculate the rebate for each customer:

data fulfillment;
set tran_track;
if tot_spend >3000 and flag = 'Target';
if 3000 < tot_spend <= 4000 then do;
r_rate = 0.03;
amount = r_rate*(tot_spend-3000);
else if tot_spend > 4000 then do;
r_rate = 0.04;
amount = r_rate*(tot_spend-4000);
Rebate = min(amount, 200);
keep custno rebate;

The rebate is calculated only for those in the target group, who spent more than $3000:

The rebate rate and rebate amount are calculated based on the spending level:

Finally, the rebate is capped at $200.

The final output data set contains two columns:

  • Custno
  • Rebate

We will now export the data set to a CSV file where the finance team can handle the rebate:

proc export data=fulfillment outfile = '/folders/myfolders/Projects/Credit Card/target1_ful.csv'
  dbms=csv replace;

Done! The CSV file is created with the rebate details: