Rabidwolff's Alehouse
"Your place for fun and knowledge."
HOME | Quotes | SAS | C# | BEER



Basics
FORMATS
INFORMATS
DATETIME
ATTRIB
LENGTH
FORMAT
INFORMAT
LABEL
JOINS
OPERATORS

Statements
RETAIN
FIRST.
LAST.

Procedures
PROC SORT
PROC COPY
PROC CPORT
IF ELSE
PROC REPORT

Functions
CAT
CATS
CATT
CATX

CDISC
Random Q & A

CDISC
CDISC Info

Coded Tools
XML Output
Examples

Table Joins Explained

Types Of Joins

  1. Left Outer Join
  2. Inner Join
  3. Outer Outer Join
  4. Full Outer Join

Tables Used for Examples:

Code to create example tables:



Table A
ID Name
1 Allan
3 Charley
5 Edward
6 Frank

Table B
ID Pet
2 Fish
3 Bird
4 Cat
6 Dog

LEFT OUTER JOIN

Keep values of Table A minus any matches between Table A and B.

Example PROC SQL Code:


Example DATA STEP Code:


Table A
ID Name
1 Allan
3 Charley
5 Edward
6 Frank

Table B
ID Pet
2 Fish
3 Bird
4 Cat
6 Dog

Table LEFT_OUTER
ID Name Pet
1 Allan
5 Edward

INNER JOIN

Keep only values that have matches in both tables A and B.

Example PROC SQL Code:


Example DATA STEP Code:


Table A
ID Name
1 Allan
3 Charley
5 Edward
6 Frank

Table B
ID Pet
2 Fish
3 Bird
4 Cat
6 Dog

Table INNER
ID Name Pet
3 Charley Bird
6 Frank Dog

OUTER OUTER JOIN

Keep all values in Tables A and B minus matches between tables A and B.

Example PROC SQL Code:


Example DATA STEP Code:


Table A
ID Name
1 Allan
3 Charley
5 Edward
6 Frank

Table B
ID Pet
2 Fish
3 Bird
4 Cat
6 Dog

Table OUTER_OUTER
ID Name Pet
1 Allan  
2   Fish
4   Cat
5 Edward  

FULL OUTER JOIN

Keep all values in Tables A and B including matches between tables A and B.

Example PROC SQL Code:


Example DATA STEP Code:


Table A
ID Name
1 Allan
3 Charley
5 Edward
6 Frank

Table B
ID Pet
2 Fish
3 Bird
4 Cat
6 Dog

Table FULL_OUTER
ID Name Pet
1 Allan  
2   Fish
3 Charley Bird
4   Cat
5 Edward  
6 Frank Dog

Rabidwolff Industries | Establisted: 10/15/2011 | Version: 4 8/4/2012 | Page Last Generatated: 9/25/2021 4:54:54 PM