24.SQL JOINS

                                               SQL JOINS

--->>WHAT IS THE DIFFERENCE BETWEEN DATASTEP MERGING AND SQL JOINS?

-->SQL JOINS DOESN'T REQUIRED "SORTED TABLES/DATASETS" .IT REQUIRED 2 OR MORE TABLES

-->HERE DOESN'T REQUIRED "COMMON" VARIABLES/COLUMNS HAVE SAME NAME IN TABLE

-->IN JOINS WE CAN USE THE COMPARISON OPERATORS OTHER THAN = EQ SIGN

-->WE CAN COMBINE UP TO 256 TABLES 

****1.IN JOINS:: TABLE WILL COMBINE "HORIZONTALLY"(CONCAT AND MERGING)

****2.IN SET OPERATORS:: TABLE WILL COMBINE "VERTICALLY"(APPENDING)

TYPES OF JOINS:::::

1.CROSS JOIN/CARTESIAN PRODUCT

2.INNER JOINS

3.RIGHT JOINS

4.LEFT JOINS

5.FULL JOINS

1.CROSS JOIN/CARTESIAN PRODUCT

--> IT WILL RETURN A NUMBER OF ROWS/OBSERVATIONS EQUAL TO THE PRODUCT OF ALL ROWS

DATA A;

INPUT ID NAME$ HT;      

CARDS;

11 AA 2

33 BB 1

55 CC 3

77 DD 4

99 EE 5

;

RUN;

PROC PRINT;

RUN;

DATA B;

INPUT ID NAME$ WT;

CARDS;

44 BB 6

55 CC 5

77 DD 5

88 EE 4

;

RUN;

PROC PRINT;

RUN;

PROC SQL;

 CREATE TABLE SAS1A AS SELECT *

 FROM A AS X CROSS JOIN B AS Y;

QUIT;

PROC SQL NUMBER FEEDBACK;

SELECT *

FROM A AS X CROSS JOIN B AS Y;

QUIT;

2.INNER JOINS

-->IT WILL RETURN/WRITE THE ROWS "COMMON TO THE BOTH TABLES"

1.METHOD WITH "WHERE" STATEMENT

PROC SQL;

SELECT *

FROM A AS X ,B AS Y

WHERE X.ID=Y.ID;

QUIT;

2.METHOD WITH "ON" STATEMENT ALONG WITH "INNER JOIN"

PROC SQL;

SELECT  *

FROM A AS X INNER JOIN B AS Y

ON X.ID=Y.ID;

QUIT;

SAME PROGRAM WITH DATASTEP

DATA MATCHH;

MERGE A(IN=XX) B(IN=YY);

BY ID;

IF XX AND YY;

RUN;

PROC PRINT;

RUN;

3.LEFT JOIN:::

-->IT WILL WRITE ALL ROWS FROM THE "LEFT TABLE(A)"

    WITH MATCHING ROWS FROM "RIGHT TABLE(B)"

PROC SQL;

SELECT *

FROM A AS X LEFT JOIN B AS Y

ON X.ID=Y.ID;

QUIT;

IN DATASTEP::

DATA LEFTJOIN;

MERGE A(IN=XX) B(IN=YY);

BY ID;

IF XX;

RUN;

PROC PRINT;

RUN;

4.RIGHT JOINS::

-->IT WILL WRITE ALL ROWS FROM "RIGHT TABLE"

 AND MATCHING ROWS WITH "LEFT TABLE"

PROC SQL;

SELECT *

FROM A AS X RIGHT JOIN B AS Y

ON X.ID=Y.ID;

QUIT;

IN DATASTEP::

DATA RIGHTJOIN;

MERGE A(IN=XX) B(IN=YY);

BY ID;

IF YY;

RUN;

PROC PRINT;

RUN;

SQL FUNCTIONS::::

-->"COALESCE" FUNCTION:::WRITE THE FIRST NON-MISSING /NULL VALUES

--> TO ADD THE MISSING RIGHT TABLE ID VALUES TO A RIGHT JOIN ADD "COALESCE" FUNCTION

PROC SQL;

SELECT COALESCE(X.ID,Y.ID) AS ID,

       COALESCE(X.NAME,Y.NAME) AS NAME,HT,WT

FROM A AS X RIGHT JOIN B AS Y 

ON X.ID=Y.ID;

QUIT;

PROC SQL;

SELECT COALESCE(X.ID,Y.ID) AS ID,

       COALESCE(X.NAME,Y.NAME) AS NAME,HT,WT

FROM A AS X left JOIN B AS Y 

ON X.ID=Y.ID;

QUIT;

5.FULL JOINS:::

--> IT WILL WRITE THE ALL ROWS FROM LEFT AND RIGHT TABLE

PROC SQL;

SELECT *

FROM A AS X FULL JOIN B AS Y

ON X.ID=Y.ID;

QUIT;

PROC SQL;

SELECT COALESCE(X.ID,Y.ID) AS ID,

       COALESCE(X.NAME,Y.NAME) AS NAME,HT,WT

FROM A AS X FULL JOIN B AS Y 

ON X.ID=Y.ID;

QUIT;

IN DATASTEP MERGE::

DATA FULL;

MERGE A B;

BY ID;

RUN;

PROC PRINT;

RUN;

--PLEASE FOLLOW THE BLOG FOR MORE INFO....

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

   CLICK HERE: https://t.me/SasAll4You