BookmarkSubscribeRSS Feed
chrisjab
Fluorite | Level 6

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%

 

4 REPLIES 4
Reeza
Super User

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
Barite | Level 11
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
Fluorite | Level 6

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

chrisjab
Fluorite | Level 6

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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1384 views
  • 0 likes
  • 3 in conversation