Edit

SAS: PROC SQL (Inner Join)

｜1
  This is the article for 'JOIN clause.'  Briefly speaking, 'JOIN clause' has two categories which are 'INNER JOIN' and 'OUTER JOIN.'  The 'OUTER JOIN' has three details, 'LEFT JOIN,'  'RIGHT JOIN,' and 'FULL JOIN.'  

  In this article, I will explain 'INNER JOIN' combining two datasets only if an observation includes in both datasets.  So, we can duplicate same detasets with using merge statement with IN function.  Please see below example.



SAMPLE DATA (Program and Results)
--------------------------------------
Both source datasets for DOMOG1 and EFFICACY1 are LINK1 and LINK2. 


***********************************************************
DEMOG1   : SUBJECT #2-#20
EFFICACY1: SUBJECT #1-#19
***********************************************************;
DATA DEMOG1    ;SET DEMOG    ; WHERE 1<SUBJECT    ; RUN ;
DATA EFFICACY1 ;SET EFFICACY ; WHERE   SUBJECT<20 ; RUN ;


DEMOG1
















EFFICACY1 (NOT all data)
















SQL Sample Program and the Results

***********************************************************
#1: INNNER JOIN
->SELECT ONLY MATCHING RECORDS FROM THE INCOMING DATASET.
-----------------------------------------------------------
EXAMPLE
WHERE CLAUSE
***********************************************************;
PROC SQL ;
  CREATE TABLE INNER1 AS SELECT EFFICACY1.DOSE,
                                EFFICACY1.SUBJECT,
                                EFFICACY1.VISIT1,
                                EFFICACY1.VISIT2,
                                EFFICACY1.DAY,
                                EFFICACY1.RES,
                             
                                DEMOG1.DRUG,
                                DEMOG1.SEX,
                                DEMOG1.AGE,
                                DEMOG1.WT
  FROM EFFICACY1, DEMOG1
  WHERE DEMOG1.DOSE   = EFFICACY1.DOSE    &
        DEMOG1.SUBJECT= EFFICACY1.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 & E1 ;
RUN ;


As 'INNER JOIN' extracts only common observation in both two tables, you can see that subject #1 was removed from created dataset of INNER2.


















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

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