Edit

SAS: PROC SQL (Outer Join: Left Join, Right Join, and Full Join)

   This is the article for 'OUTER JOIN clause' which has three details, 'LEFT JOIN,'  'RIGHT JOIN,' and 'FULL JOIN.'  In this article, I will show these three 'OUTER JOIN.'


SAMPLE DATA (Program and Results)
--------------------------------------
Please see previous blog (LINK). 






SQL Sample Program and the Result (Left Join)


***********************************************************
OUTER JOIN
->SELECT ALL RECORDS (EVEN IF NON-MATCHING ONES) FROM ONE OR
  MORE OF THE INCOMING DATASETS.
***********************************************************;

PROC SQL ;
CREATE TABLE OUTER1 AS
 /**********************************
  DOSE AND SUBJECT ARE OVERLAPPED WITH
  THESE DATASETS. LOG SHOWS WORNING. 
  **********************************/
 SELECT DEMOG1.*, EFFICACY1.*

 FROM DEMOG1 AS D1 LEFT JOIN EFFICACY1 AS E1
 ON D1.DOSE    =E1.DOSE    &
    D1.SUBJECT =E1.SUBJECT
;
QUIT ;

***********************************************************
THIS IS SAME AS ABOVE SQL.
***********************************************************;
PROC SORT DATA=DEMOG1    ; BY DOSE SUBJECT; RUN ;
PROC SORT DATA=EFFICACY1 ; BY DOSE SUBJECT; RUN ;
DATA INNER2 ;
 MERGE DEMOG1(IN=D1) EFFICACY1(IN=E1) ;
    BY DOSE SUBJECT ;
    IF D1 ;
RUN ;

OUTPUT
















SQL Sample Program and the Result (Right Join)
***********************************************************
WORNING:
-> 'WHEN SQL ENCOUNTERS VARIABLES OF THE SAME NAME IN BOTH DATASET,
   IT KEEPS THE VALUE OF THE FIRST-SEEN DATASET.'
-> FIRST SEEN DATASET(DEMOG1) HAS NO SUBJECT=1, SO THIS WILL BE BLANK for CREATED DATASETS  
-> MAKE SURE SOURCE TWO DATASETS INCLUDE ALL RECORDS.
***********************************************************;
PROC SQL ;
CREATE TABLE OUTER2 AS
 /**********************************
  DOSE AND SUBJECT ARE OVERLAPPED WITH
  THESE DATASETS. LOG SHOWS WORNING. 
  **********************************/
 SELECT DEMOG1.*, EFFICACY1.*

 FROM DEMOG1 AS D1 RIGHT JOIN EFFICACY1 AS E1
 ON D1.DOSE    =E1.DOSE    &
    D1.SUBJECT =E1.SUBJECT
;
QUIT ;

OUTPUT

















SQL Sample Program and the Result (Full Join)
***********************************************************
WORNING: SAME PROBLEM AS RIGHT JOIN
***********************************************************;
PROC SQL ;
CREATE TABLE OUTER3 AS
 /**********************************
  DOSE AND SUBJECT ARE OVERLAPPED WITH
  THESE DATASETS. LOG SHOWS WORNING. 
  **********************************/
 SELECT DEMOG1.*, EFFICACY1.*

 FROM DEMOG1 AS D1 FULL JOIN EFFICACY1 AS E1
 ON D1.DOSE    =E1.DOSE    &
    D1.SUBJECT =E1.SUBJECT
;
QUIT ;


OUTPUT













***********************************************************
EXAMPLE:#2-3-2:FULL  JOIN
-----------------------------------------------------------
Dealt with warning to use COALESCE statement.
***********************************************************;
PROC SQL ;
CREATE TABLE OUTER4 AS
 SELECT COALESCE (DEMOG1.SUBJECT, EFFICACY1.SUBJECT) AS SUBJECT,
        COALESCE (DEMOG1.DOSE   , EFFICACY1.DOSE   ) AS DOSE,
        DRUG, SEX, WT, VISIT1, VISIT2, DAY, RES

 FROM DEMOG1 AS D1 FULL JOIN EFFICACY1 AS E1
 ON D1.DOSE    =E1.DOSE    &
    D1.SUBJECT =E1.SUBJECT
;
QUIT ;

OUTPUT













--------------------------------------  

SOURCE: 
Susan P Marcella and Gail Jorgensen. "PROC SQL: Tips and Translations for Data Step Users."  SUGI2009. PDF Files.  LINK




Lists of articles for PROC SQL

1. SAS: PROC SQL (Over All)
2. SAS: PROC SQL (Select... From) 
3. SAS: PROC SQL (Where)
4. SAS: PROC SQL (Where & Two New Tables)
5. SAS: PROC SQL (Order By)
6. SAS: PROC SQL (Group By)
7. SAS: PROC SQL (Summary for All (Across Group)
8. SAS: PROC SQL (Case When)
9.  SAS: PROC SQL (Cross Join for CARTESIAN PRODUCT)
10. SAS: PROC SQL (Union and UNION Corresponding)
11. SAS: PROC SQL (Outer Union Corresponding)
12. SAS: PROC SQL (Inner Join)
13. PROC SQL (Outer Join:  Left Join, Right Join, and Full Join)


Search This Blog