Desktop productivity for business analysts and programmers

Append Slot and Table games datasets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Append Slot and Table games datasets

Based on the advices of this site, I append Slot and Table games datasets and was able to combine slot and table players together.  Joining causes the lesser table set (Table) to expand its rows to match with Slot if a Customer ID matches in both datasets, thereby substantially inflate my revenue.

However, appending creates another problem for me. It does not combine the PlayerDayDate together.  If you check the image below with the highlighted rows, customer 43714 play date of 12APR2015 should be on one row not two and any associated revenue columns should also be combine.  How do I make this happen?  How can I combine two same playdates into one row.

Capture.PNG


Accepted Solutions
Solution
‎05-12-2015 03:48 PM
Super User
Posts: 11,105

Re: Append Slot and Table games datasets

Append does just that: add one set of data at the bottom of another so the result is the that the final data set has all records from all contributing tables.

If you had previous questions with details of your data please add a reference link to those previous threads.

You may need to do another step with the combined data such as summarize something like:

proc summary data=have nway;

class guestnumber playerdaydate;

var revenuevariable;  /* if more than one variable then put all of them here*/

output out=want (drop=_type_) sum=;

run;

This will generate a total of the variables on the VAR clause for each combination of guestnumber and playerdaydate in the data.

View solution in original post


All Replies
Solution
‎05-12-2015 03:48 PM
Super User
Posts: 11,105

Re: Append Slot and Table games datasets

Append does just that: add one set of data at the bottom of another so the result is the that the final data set has all records from all contributing tables.

If you had previous questions with details of your data please add a reference link to those previous threads.

You may need to do another step with the combined data such as summarize something like:

proc summary data=have nway;

class guestnumber playerdaydate;

var revenuevariable;  /* if more than one variable then put all of them here*/

output out=want (drop=_type_) sum=;

run;

This will generate a total of the variables on the VAR clause for each combination of guestnumber and playerdaydate in the data.

Frequent Contributor
Posts: 104

Re: Append Slot and Table games datasets

Thank you for replied.  It just what I wanted.  However how do I clean it up a bit, please see result below.

Capture.PNG

I want just to include row 11 to 18 and take out the FREQ?

Super User
Posts: 11,105

Re: Append Slot and Table games datasets

It looks like you didn't set the NWAY option that is why you get summaries by  date and guest as well as the guest/date combinations. Add _freq_ to the drop in the output.

If you didn't have the drop= _type_ there would have been a variable _type_ with values 0 through 3 with _type_=0 sum of all records, type=1 sum of all clients, type=2 sum of all dates and _type_=3 sum by client and date. This is a useful feature for some applications.

Frequent Contributor
Posts: 104

Re: Append Slot and Table games datasets

Thank you got it.  Using your advice, was able to clean it up.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 307 views
  • 1 like
  • 2 in conversation