Desktop productivity for business analysts and programmers

joined three tables result is not pretty

Reply
Frequent Contributor
Posts: 104

joined three tables result is not pretty

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

Grand Advisor
Posts: 17,411

Re: joined three tables result is not pretty

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?

Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

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
Grand Advisor
Posts: 17,411

Re: joined three tables result is not pretty

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

Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

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

Esteemed Advisor
Posts: 6,698

Re: joined three tables result is not pretty

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

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

Grand Advisor
Posts: 17,411

Re: joined three tables result is not pretty

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.

Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

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

Grand Advisor
Posts: 17,411

Re: joined three tables result is not pretty

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?

Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

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?

Grand Advisor
Posts: 17,411

Re: joined three tables result is not pretty

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.

Frequent Contributor
Posts: 104

Re: joined three tables result is not pretty

Thank you all!

Ask a Question
Discussion stats
  • 12 replies
  • 539 views
  • 6 likes
  • 3 in conversation