Append Slot and Table games datasets

[sdang]

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.

[ballardw · Accepted solution]

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.

[sdang]

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

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

[ballardw]

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.

[sdang]

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