BookmarkSubscribeRSS Feed
sdang
Quartz | Level 8

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 1PlayDateTrips
Customer 12/12/2014  1:15am1
Table2PlayDateTrip
Customer 12/12/2014 2:00pm1
8 REPLIES 8
Reeza
Super User

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

sdang
Quartz | Level 8

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
Super User

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
Onyx | Level 15

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
Quartz | Level 8

Thank you!

ChrisHemedinger
Community Manager

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:

sampledata.png

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.)

sampleresult.png

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
sdang
Quartz | Level 8

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?

Capture.PNG

sdang
Quartz | Level 8

Thank you Reeza and Hai.kuo.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8 replies
  • 1155 views
  • 6 likes
  • 4 in conversation