Trips from two tables

[sdang]

Hi, I have two data tables: Table1 and Table2. I want to combine the trips based on the PlayDate.  I don't want this syntax "T1 Trips + T2 Trips" cause this will give me 2 instead of 1 because Customer 1 played only 1 time in the date 2-12-2014:

Table 1	PlayDate	Trips
Customer 1	2/12/2014  1:15am	1
Table2	PlayDate	Trip
Customer 1	2/12/2014 2:00pm	1

[Reeza]

What do you mean by combine? Post sample output please.

[sdang]

I deal with casino data, when a customer game in at our
property on a given day we counted them at one trip overall.
But If I played at table games at 1:00PM, TableGames rating system will
count me as one trip.  However, if I moved
to slot games at 2:00PM, SlotGames rating system will count be as one trip.
This is how our SAS databases are structured.
I want the trip for that one day at our gaming premise.   The syntax "T1 Trips + T2 Trips" will give me 2 trips total in 2-12-2014; even though I only game one time at the property.

[Reeza]

I'm sorry, I don't understand what your output should be. Based on the data you've provided what do you want the output to look like?

[Haikuo]

Assume both of your SAS tables share the identical variables, a simple approach could be the following 2-3 steps:

1. Append both tables (SET / PROC APPEND/ PROC SQL UNION)

2. Sort it by Customer_ID and Game_date (PROC SORT/PROC SQL/HASH etc)

3. Dedup using PROC SORT/DATA STEP -- FIRST.VAR

Been saying so much, I am just as confused as Reeza without knowing anything about your data structure and your expected outcome.

Haikuo

[sdang]

Thank you!

[ChrisHemedinger]

I have some limited experience with gaming data.  From your question, I assume that you are getting one table per game "source", and you wish to combine these sources to see a larger picture of a customer's total activity.  You want to understand the activity "per trip", which maybe you're defining as activity within a single gaming day.  In the casino world, the "gaming day" usually does not start at midnight but at some other time defined either by the casino or a regulatory agency.  For example, the gaming day might begin at 6:00 AM, even though the casino operates on a 24-hour schedule.

So let's generate some sample data for two games with customer IDs and their playing times:

/* creat sample data - one table per game */
data slots;
  length customerId $ 15 timestamp 8;
  infile datalines dsd;
  format timestamp datetime20.;
  input customerId timestamp:anydtdtm20.;
  datalines;
1001, 01/04/2015 1:15
1002, 01/04/2015 1:16
1003, 01/04/2015 12:30
1001, 01/04/2015 16:15
;

data blackjack;
  length customerId $ 15 timestamp 8;
  infile datalines dsd;
  format timestamp datetime20.;
  input customerId timestamp:anydtdtm20.;
  datalines;
1010, 01/03/2015 23:17
1011, 01/04/2015 00:30
1012, 01/04/2015 03:15
1001, 01/05/2015 01:15
;

This results in two tables, with some of the records pertaining to the same customer:

Next, let's combine those data sources and "tag" each record for the game played (slots or blackjack).  Also, assuming a 6:00 AM start to the gaming day, we'll create a GAMINGDAY date field that normalizes the data according to the calculated gaming day.  Finally, we'll sort the output by gaming day and customer ID, so we can easily see how much activity a customer had on a particular day.

/* Assuming gaming day begins at 06:00 AM */
data allgames;
  length game $ 10 gamingday 8;
  format gamingday DATE9.;
  set slots(in=s)
        blackjack(in=b);

  /* segment the games */
  if (s) then
    game="SLOTS";
  else if (b) then
    game="BLACKJACK";

  /* Adjust for gaming day */
  if timepart(timestamp)<'06:00:00't then
    gamingday=datepart(timestamp)-1;
  else gamingday=datepart(timestamp);
run;

/* output the data, grouping by game day and customer */
proc sort data=allgames out=allgames;
  by gamingday customerId;
run;

The result shows how a customer played multiple games in a single gaming day.  Notice how even when the timestamps span two calendar days, the "gaming day" groups the customer activity together.  (When does this customer sleep, you wonder?  That's a question that casinos probably don't worry too much about.)

What you do next with this data will depend on your purpose.  You might combine it with other data about money spent on games or on meals, seasonal activity data, and whatever else you know about the customer to plan the best offers that a customer might respond to.  Or perhaps you're just looking for more of an operational view of gaming activity at the customer/patron level.

Chris

[sdang]

Hi Chris,

You are on the spot about "per trip" as defines as total activities in one gaming day. If a guest plays table games and slot games in one day, our current data structure shows up in two tables.  Below I used inner join for  three tables: Guest, Slot, and Table.  The result looks like this.

As you can see guest 1001 played total times in one day in TablePlayerDayDate1 even though he/she did not have multiple transactions.  Now how do I get their trips based on their one gaming day?

[sdang]

Thank you Reeza and Hai.kuo.