joined three tables result is not pretty

[sdang]

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?

[Reeza]

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]

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

Sample Data.

SlotGuestNumber	SlotPlayDate	TableGuestNumber	TablePlayDate
1001	4-Mar-14	1001	4-Mar-14
1001	11-Mar-14
1001	18-Mar-14

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

GuestNumber	PlayDate
1001	4-Mar-14
1001	11-Mar-14
1001	18-Mar-14

[Reeza]

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

[sdang]

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

[Kurt_Bremser]

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]

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]

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]

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.

Slot DataSet

Table dataset

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?

[Reeza]

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]

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]

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]

Thank you all!