University:
Boston University
Course:
GRS CS 910 | Directed Study: Systems
Academic year:

2022
Views:

347

Pages:

21
Author:

Edgar Rowe

DATA STEP PRESENTING YOUR DATA The DATA step code below produced the block-oriented report seen previously. data _null_; set heartsorted; file print notitles header=head; put @10 'Patient ID: ' pat_id ' sex / @30 'Gender: ' height/ @30 'Height: ' weight/ @30 'Weight: @30 'Cholesterol: ' cholesterol //; return; head: put @22 'Patient Information' //; return; run; C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . JOINING SAS DATA USING THE DATA STEP AND PROC SQL C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 17 TYPES OF JOINS • Natural • Uses no ‘keys’ – typically termed a Cartesian product • Inner • Outer Joins • Left • Right • Full JOINING DATA • One to One • One to Many • Many to One • Many to Many 18 ONE TO ONE ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 19 MANY TO MANY TYPES OF JOINS • Inner Join • The intersection of two or more sets • Return only matching rows A B C C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 20 SAMPLE DATA DEFAULT INNER JOIN ONE TO ONE proc sql; /* create table SQL_Join as*/ select a.*, b.fruits from Left a , Right b where a.key = b.key ; quit; 21 DEFAULT JOIN ONE TO ONE data Data_Merge; merge Left Right; by key; run; INNER JOIN ONE TO ONE data Data_Inner; merge Left(in=left) Right(in=right); by key; if left and right; run; 22 INNER JOIN ONE TO ONE proc sql; select a.*, b.fruits from Left a inner join Right b on a.key = b.key ; quit; C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . OUTER JOINS • • Return all matching rows, plus nonmatching rows from one or both tables Can be performed on only two tables or views at a time. Left Full Right 23 OUTER JOINS • Left Join • Retrieve the matching rows as well as the non-matches from the left table Left SAMPLE DATA 24 LEFT JOIN ONE TO ONE proc sql; select a.*, b.fruits from Left a Left join Right b on a.key = b.key ; quit; LEFT JOIN ONE TO ONE data Data_Left; merge Left(in=left) Right(in=right); by key; if left; run; 25 OUTER JOINS • Right Join  Retrieve the matching rows as well as the non-matches from the right table Right SAMPLE DATA 26 RIGHT JOIN ONE TO ONE proc sql; select b.key as Key , a.Veggies , b.Fruits from Left a right join Right b on a.key = b.key ; quit; RIGHT JOIN ONE TO ONE data Data_Right; merge Left(in=left) Right(in=right); by key; if right; run; 27 OUTER JOINS • Full Join • Retrieve the matching rows as well as the non-matches from the left table and the non-matches from the right table. Full SAMPLE DATA 28 FULL (OUTER) JOIN ONE TO ONE proc sql; /* create table SQL_Outer as*/ select a.key as Key , a.Veggies , b.Fruits from Left a Full join Right b on a.key = b.key ; quit; THE COALESCE FUNCTION The COALESCE function returns the value of the first non-missing argument. General form of the COALESCE function: COALESCE(argument-1,argument-2<, ...argument-n) 29 FULL (OUTER) JOIN ONE TO ONE proc sql; /* create table SQL_Outer as*/ select coalesce(a.key,b.key) as Key , a.Veggies , b.Fruits from Left a Full join Right b on a.key = b.key ; quit; FULL (OUTER) JOIN ONE TO ONE data Data_Outer; merge Left(in=left) Right(in=right); by key; run; 30 SAMPLE DATA Left Right ONE TO MANY * Code is the same, only the data have changed. C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . DEFAULT JOIN ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 31 INNER JOIN ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . LEFT JOIN Note the row order! ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 32 RIGHT JOIN ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FULL (OUTER) JOIN ONE TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 33 MANY TO MANY PROC SQL C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . MANY TO MANY DATA STEP MERGE C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . NOTE: MERGE statement has more than one data set with repeats of BY values. 34 INNER JOIN MANY TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . LEFT JOIN MANY TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 35 RIGHT JOIN MANY TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . FULL (OUTER) JOIN MANY TO MANY C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 36 JOINING DATA SUMMARY • SQL joins and DATA step merges may produce the similar output, other than row order, for the following joins: • One to one • One to many • SQL joins process many to many data correctly. • The DATA step MERGE statement will not correctly perform a many to many join. This requires additional coding within the DATA step. 37

PROC SQL Vs. Data Step Processing Ch 2

Related Documents

Recommended Documents

Description

Related Documents

Free up your schedule!

Take 5 seconds to unlock