This week's SAS tip is from Susan Slaughter and Lora Delwiche's bestselling The Little SAS Book for Enterprise Guide 4.2. Susan and Lora are revered in the user community. And their work continues to help SAS users throughout the world. I hope you'll also find value in this week's excerpt.
The following excerpt is from SAS Press authors Susan Slaughter and Lora Delwiche and their book "The Little SAS Book for Enterprise Guide 4.2" Copyright © 2010, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)
5 Comments
What type of join should I use to report only unmatched rows from two tables?
For example, I have two datasets from different months of server configurations and I want to know which server's configuration changed from one month to the next.
This blog posting is an excerpt from our book "The Little SAS Book for Enterprise Guide 4.2." It's section 6.1. We do cover appending; that's section 6.2. We also show a many-to-many merge; that's section 6.3. If you want to know more about these, you might want to buy the book. :-)
The only difference between appending and stacking is that appending is the technical term and stacking is the non-technical term. They are just two words for the same thing. Imagine that you somehow have two data sets physically sitting on your desk. If you wanted to append them, then you would stack one on top of the other. If you wanted to merge them, then you would put one next to the other.
Appending is pretty simple. Merging is much more complex. (Joining, by the way, is just another term for merging and both are technical terms.) The diagrams in this section show what happens with different kinds of merges. If you look closely at the diagrams and actually study them, I think you will see what happens. However, section 6.2 shows a many-to-many merge with real data so you might want to look at that too.
A many-to-many merge is also called a Cartesian join. One interesting thing about a many-to-many match merge is that it is the default for SQL. Of course, if you write a SAS program you can also join data sets using a MERGE statement in a DATA step. However, it is IMPOSSIBLE to do a many-to-many match merge with a DATA step.
In real life, it is extremely rare that you want to do a many-to-many merge. The inventors of SQL had a pie-in-the-sky attitude when they made the default something that almost no one wants (and that uses a lot of computer resources). The inventors of the DATA step were very practical so they made the default something that you actually do want.
can you explain what happends when many-to-many match (with merge ou sql) ? Thanks
"Appending Tables is like stacking them" Well why not explain the difference and cover stacking at the same time?
Hi Bob,
Thank you for reading the excerpt. Append is covered in the book. The SAS author's tip series provides free and short excerpts to highlight the great information you can get from SAS Press books.