Module 17 Sas Proc Sql Joins

Front 1

Types of joins

Back 1

horizontal,

inner,

outer,

left outer,

right outer,

full,

natural,

carteisan,

self(reflexive)

Front 2

equijoin

Back 2

uses equality to determine matching rows

Front 3

join-keys

Back 3

ON or WHERE

Front 4

Inner join

Back 4

return only matching rows. syntax: proc sql; select * from customers, transactions where customers.ID=transactions.ID; quit;

Front 5

outer joins

Back 5

return all matching rows plus nonmatching rows from one or both tables (union)

Front 6

Carteisian join

Back 6

also called a cross join.

one row for every combination of rows from two tables (number of rows in table A) x (nubmer of rows in table B).

example 3 x 3 = 9. Each column from each table is represented in the join. Matching columns appear in the join for the number of tables being joined. If both Table A and Table B have "ID", the join will have two "ID" columns.

Front 7

Natural join

Back 7

automatically selects columns with same name and type for determining matching rows. (Inner by default by could be outer)

Front 8

Number of rows in the result of joins

Back 8

inner join is less than or equal to outer join less than or equal to Cartesian product

Front 9

Qualification

Back 9

the specification of the table names in the column reference in the where clause is called "qualification"

Front 10

Alias

Back 10

the table name may be abbreviated with an alias.
Syntax: proc sql; select * from customers as c, transactions as t where c.ID=t.ID; quit;

Front 11

Difference between SQL join and datastep merge

Back 11

datastep merge requires a proc sort for both tables before merging. also finds first occurrence of the match then creates observation, then moves pointer to next obs in both datasets. Also automatically overlays columns. PROC SQL selects ALL (combinations of) rows with matching values of ID, does not overlay columns.

Front 12

Alternative syntax for label

Back 12

select employee_name as Employee Name

Hint 12

or put in quotes:

Front 13

month(Birth_Date)

Back 13

will return the month number out of the sequence from Bith_Date

Front 14

What will this do? ORDER BY 4

Back 14

This will sort the report in ascending order of values in the fourth selected column in the select statement. quick way to order rather than typing it out.

Front 15

Alternative (more descriptive) inner join syntax

Back 15

PROC SQL; TITLE "BROUGHT TO YOU BY SQL ALTJOINSYNTAX"; SELECT * FROM tableA as A INNER JOIN TableB as B ON A.ID=B.ID; QUIT;

Front 16

natural joins

Back 16

Adopts columns with same name, same type as join-keys. Includes all columns, only one of the matching columns and only rows with matching values on the matching column. syntax example: PROC SQL; TITLE "You make me feel like a natural join"; SELECT * FROM tableA NATURAL JOIN tableB; QUIT;

Front 17

Outer join - Left Join

Back 17

Will include all rows from the first (or left table) and all columns from the other table associated with any value that matches that in the matched column of the left table. syntax: proc sql; title "left join (all customers); selct * from customers as c LEFT JOIN transactions as t on c.id=t.id; quit;

Front 18

Outer join - Right Join

Back 18

Will include all rows from the second table (or left table) and all columns from the other table associated with any value that matches that in the matched column of the right table. syntax: proc sql; title "right join (all customers); selct * from customers as c RIGHT JOIN transactions as t on c.id=t.id; quit;

Front 19

Outer join - FULL JOIN

Back 19

will include all columns of each table. Matching columns will be included for each table. syntax: proc sql; select * from customers as c FULL JOIN transactions as t on c.id=t.id; quit;

Front 20

Coalesce

Back 20

returns the first non-missing argument. All arguments must be same type. When all are missing, missing value is returned. Basically, it will combine matching columns. syntax: proc sql; select coalesce(c.id,t.id) as id, name, action, amount from customers as c FULL JOIN transactions as t on c.id=t.id; quit;

Front 21

Comparison of join types

Back 21

table limit: 256 for both inner and outer. join behaviour: inner-returns matching rows, outer-returns matching and nonmatching rows. Join OPtiosn: Inner-Matching rows only, Outer-LEFT, FULL, RIGHT. Syntax changes: Inner-Multiple tables separated by commas in the FROM clause & Where clause that specifies join criteria, Outer-ON cluase that specifies join criteria