02-23-2015 08:06 PM
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:
|Customer 1||2/12/2014 1:15am||1|
|Customer 1||2/12/2014 2:00pm||1|
02-23-2015 08:36 PM
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.
02-23-2015 09:59 PM
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.
02-24-2015 08:31 AM
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:
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.
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.
02-25-2015 03:16 PM
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?