02-25-2015 05:50 PM
In the casino industry, trip defines as total activities
within day. A guest might play table
games then turn around and play slot games the next hour; we count that as 1
trip. However, our data structure
divided Slot and Table separate. A guest
can be In two tables within a given day. Below I used inner join for three
tables: Guest, Slot, and Table. The
result looks like the picture below. 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?
02-25-2015 07:30 PM
Based on your previous question, I think you're supposed to append the tables not join them.
Can you please simplify the problem, post a single data sample, not a picture, and the expected output from that sample data?
02-25-2015 07:45 PM
I'm sorry about the confusion. I'm new to SAS Guide and Communities. Please see below. I hope it helps.
I want it to look like below, regardless of time stamps.
02-26-2015 01:33 AM
I try to specify the situation:
- 1 dataset for holding guest data
- 1 dataset for slot playing
- 1 dataset for table playing
All datasets have the Guestnumber in common, although with different names; the same is true for the dates.
by GuestNumber PlayDate;
Now you can join the dataset want with your basic guest dataset on GuestNumber.
02-26-2015 01:12 PM
Hi, I do have dataset Guest but it does not have PlayDate. I want to combine SlotGuestNumber and TableGuestNumber under the a new column GuestNumber. Same goes with SlotPlayDate and Table PlayDate under a new column "PlayDate".
02-26-2015 01:58 PM
It may be best if you post what each of your three tables look like and then what you want your output is, if you don't understand @kurtbremser solution or my suggestions.
I agree with Kurt and you're not trying to join tables, you need to append (stack) them. To do this, you need to rename the variables so that they have the same name and then use the append task or do a UNION in SQL. I'm not sure if you can do a UNION operation in EG Query Builder.
In creating the stacked file I would create an ID indicating Slot or Table so you can identify the source, more than likely this will be important in a future step.
Once you have the stacked file, you can use the SORT task/procedure to remove duplicates. Or you can use DISTINCT in Query Builder to achieve the analysis you want.
02-26-2015 02:20 PM
I'm trying all of suggestions here. Still new to SAS so it takes some times to see if they work. Much appreciated. Below are images of my dataset. In order: Guest, Slot, Table and result.
Below is the result I wanted but as you can see how SlotPlayDate data repeats itself until a new date was available. I don't want that to happen. How can I create a new column that combines SlotPlayDate and TablePlayerDayDate1 together?
02-26-2015 02:31 PM
From what I see, the guest table does not add any information so you can ignore it for now.
I don't think the table you're looking to create will make sense and will be useful for analysis, in my experience.
Perhaps the following might work:
Guest Number PlayDate Game
3 09Jan2014 Slot
3 09Jan2014 Table
3 09Jan2014 Slot
What type of analysis are you trying to perform?
02-26-2015 02:48 PM
That could work on some cases. But what if I do a count-we call it 'gaming trips'- and it defines as one gaming day. In this case GuestNumber 3 will have three gaming trips. I do I make the date distinct?
02-26-2015 02:55 PM
I'm not familiar with that metric - gaming trips - if its the number of trips per year or something you can calculate that in multiple ways and distinct is one way.
I wouldn't necessarily change the data structure, I would use distinct in the query.
Or you can use a summary procedure to collapse by guest/date, assuming you also have amounts attached to each of those slot/table then you can also summarize the total amounts per day/game or overall if required. You wouldn't be able to do that with your other structure.