Report on Credit/Debit/ Balance Transactions using a First In First Out Approach

[chrisjab]

Good Afternoon, 

I am trying to draft a reward redemption project on sas eg and I am facing some difficulties.

Currently I have 2 Tables:

- The first one contains the following information:

   MembershipID

   Credit (in rewards points)

   Rewards Issued Date

   Source Of Business (Restaurant A, Restaurant B ...)

- The second one contains the following inofrmation:

   MembershipID

   Debit (in rewards point)

   Redemption Date

My goal is to apply a FIFO approach to measure the points redemption by Source of Business based on the dates the points are redeemed. For example, if I start with a zero point balance and I am issued 1,000 points at Restaurant A on 12/10/2016 and 1,500 points at Restaurant B on 12/15/2016; my running balance as of 12/15/2016 becomes 2,500 - let's say now that I redeem 1,500 points on 12/20/2016, I want my query to show a 1,000 deduction from my first credit transaction (Restaurant A) then 500 from second credit transaction (Restaurant B) so that my SOB Redemption Report shows 100% from Restaurant A and 33% from Restaurant B 

INPUT                            

                            SOB              Credit          Debit         Balance

12/10/2016    Restaurant A       1,000              0                1,000 

12/15/2016    Restaurant B       1,500              0                2,500

12/20/2016        ------------              0             1,500            1,000   

OUTPUT

                             SOB              Redemption%

12/20/2016     Restaurant A              100% 

12/20/2016     Restaurant B                33%

[Reeza]

The redemption on 20th doesn't show a company name? Is that on purpose or because you didn't want to type it out?

Please post some a larger sample data as well as the expected output.

Make sure to include all your 'edge' cases. 

[rogerjdeangelis]

Report on Credit/Debit/ Balance Transactions using a First In First Out Approach

Not tested but it seems to work.

inspired by
https://goo.gl/M7qoKq
https://communities.sas.com/t5/SAS-Enterprise-Guide/Report-on-Credit-Debit-Balance-Transactions-using-a-First-In/m-p/347946


HAVE ( I added customer id and a second sample )
================================================

Up to 40 obs WORK.HAVE total obs=6

Obs    CID     DATE            SOB         CREDIT    DEBIT    BALANCE

 1     A01    12/10/2016    RestaurantA      1000         0      1000
 2     A01    12/15/2016    RestaurantB      1500         0      2500
 3     A01    12/20/2016    ------------        0      1500      1000
 4     A02    12/10/2016    RestaurantA      1000         0      1000
 5     A02    12/15/2016    RestaurantB      1500         0      2500
 6     A02    12/20/2016    ------------        0      2500         0

WANT
====

Up to 40 obs WORK.WANT total obs=4

                                                    REDEMTION_
Obs    CID     DATE             SOB        CREDIT      PCT

 1     A01    12/10/2016    RestaurantA     1000     100.000  1500 debit ( 1000 credit  - 1500 debit = 1005 redeemed
 2     A01    12/15/2016    RestaurantB     1500      33.333   500/1500 = 33% redemption percent

 3     A02    12/10/2016    RestaurantA     1000     100.000
 4     A02    12/15/2016    RestaurantB     1500     100.000

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

data have;
informat cid $4. date mmddyy10. sob $24. credit debit balance comma12.;
INPUT cid date SOB Credit Debit Balance;
cards4;
A01 12/10/2016 RestaurantA 1,000 0 1,000 .
A01 12/15/2016 RestaurantB 1,500 0 2,500 .
A01 12/20/2016 ------------ 0 1,500 1,000
A02 12/10/2016 RestaurantA 1,000 0 1,000 .
A02 12/15/2016 RestaurantB 1,500 0 2,500 .
A02 12/20/2016 ------------ 0 2,500 0
;;;;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

data want(where=(not (sob=:'------')));
  retain debit_next debit_keep  dif 0;
  do until(last.cid);
    set have;
    by cid;
    debit_keep=debit_keep + debit;
  end;
  debit_next=debit_keep;
  do until(last.cid);
    set have;
    by cid;
    dif=credit - debit_next;
    if dif <= 0 then do;
         debit_next=abs(dif);
         redemtion_pct=100;
    end;
    else do;
       redemtion_pct=100*debit_next/credit;
    end;
    keep cid date sob  credit redemtion_pct;
    output;
  end;
  debit_next=0;
  debit_keep=0;
  dif=0;
run;quit;

/*
NOTE: There were 6 observations read from the data set WORK.HAVE.
NOTE: There were 6 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
*/

[chrisjab]

Thank you Roger, I will try it this morning  and keep you posted 

[chrisjab]

Reeza, the rewards points can be redeemed anywhere; however, they are issued at the restaurants - We want to calculate the redemption rate based on where the rewards are issued