BookmarkSubscribeRSS Feed
sdang
Quartz | Level 8

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?

Capture.PNG

12 REPLIES 12
Reeza
Super User

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?

sdang
Quartz | Level 8

I'm sorry about the confusion.  I'm new to SAS Guide and Communities. Please see below.  I hope it helps.

Sample Data.
SlotGuestNumberSlotPlayDateTableGuestNumberTablePlayDate
10014-Mar-1410014-Mar-14
100111-Mar-14
100118-Mar-14

I want it to look like below, regardless of time stamps.

GuestNumberPlayDate
10014-Mar-14
100111-Mar-14
100118-Mar-14
Reeza
Super User

That looks like you just want the first two columns? So ignore the last two or drop them?

sdang
Quartz | Level 8

Hi, I want to combine SlotGuestNumber and TableGuestNumber under the a new column GuestNumber.  Same goes with SlotPlayDate and Table PlayDate.

Kurt_Bremser
Super User

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.

My suggestion:

data intermediate;

set

  slotplaying (

    keep=SlotGuestNumber1 SlotPlayDate

    rename=(SlotGuestNumber1=GuestNumber SlotPlayDate=PlayDate)

  )

  tableplaying (

    keep=TableGuestNumber2 TablePlayDate1

    rename=(TableGuestNumber2=GuestNumber TablePlayDate1=PlayDate)

  )

;

run;

proc sort

  data=intermediate

  out=want

  nodupkey

;

by GuestNumber PlayDate;

run;

Now you can join the dataset want with your basic guest dataset on GuestNumber.

sdang
Quartz | Level 8

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

Reeza
Super User

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.

sdang
Quartz | Level 8

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.

Capture.PNG

Slot DataSet

CaptureSlot.PNG

Table dataset

CaptureTable.PNG

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?

CaptureCombine.PNG

Reeza
Super User

Ok

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?

sdang
Quartz | Level 8

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?

Reeza
Super User

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.

sdang
Quartz | Level 8

Thank you all!

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
  • 12 replies
  • 1542 views
  • 6 likes
  • 3 in conversation