25.SET OPERATORS

                                    SET OPERATORS


1.UNION(ALL , CORR) 

2.OUTER UNION (CORR)

3.EXCEPT (ALL , CORR)

4.INTERSECT (CORR)


KEYWORDS::::

-->ALL

-->CORR(CORRESPONDING)


IN JOINS: DATASETS COMBINE "HORIZONTALLY"

IN SET OPERATORS: DATASETS COMBINE "VERTICALLY"



DATA AA;

INPUT XX YY;

CARDS;

11 56

11 32

11 75

66 78

77 69

88 85

;

RUN;


DATA BB;

INPUT XX ZZ;

CARDS;

11 32

11 75

44 63

33 96

66 85

66 75

55 66

;

RUN;


1.UNION(ALL N CORR) 

--->IT WILL DISPLAY ALL THE ROWS FROM BOTH TABLES AND REMOVE THE "DUPLICATES" FROM THE COMBINED TABLE

ALL:: IT WILL ALLOW THE "DUPLICATES" FROM COMBINED TABLES

CORR:: IT WILL MATCH THE COLUMNS IN TABLES "BY NAMES" BUT NOT WITH POSITIONS


PROC SQL NUMBER;  

SELECT *

FROM AA UNION 

SELECT *

FROM BB;

QUIT;

 

UNION ALL: ALLOW THE DUPLICATES


PROC SQL NUMBER;

SELECT * 

FROM AA UNION ALL

SELECT * FROM BB;

QUIT;


UNION CORR:: IT WILL MATCH THE COLUMNS IN TABLES "BY NAMES" BUT NOT WITH POSITIONS


PROC SQL NUMBER;

SELECT * 

FROM AA UNION CORR

SELECT * FROM BB;

QUIT;


2.OUTER UNION (CORR)


-->IT WILL CONCATENATE THE TWO TABLES/IT EQUALS TO SET STATEMENT IN DATASTEP

-->IT WILL ALLOW THE DUPLICATES IN CONCATENATION TABLES

-->NO REQUIRED "ALL" KEYWORD

-->IT USE ONLY "OUTER UNION CORR"


PROC SQL NUMBER;

SELECT * 

FROM AA OUTER UNION CORR

SELECT * FROM BB;

QUIT;


3.EXCEPT OPERATOR:

-->IT WILL WRITE THE UNIQUE ROWS FROM FIRST TABLES

       THAT ARE NOT IN 2ND TABLES(NON-MATCHING)

-->REMOVES THE DUPLICATES RECORDS


PROC SQL NUMBER;

SELECT * 

FROM AA EXCEPT

SELECT * FROM BB;

QUIT;

                                                    

EXCEPT ALL:IT WILL WRITE UNIQUE ROWS FROM FIRST TABLES THAT ARE NOT IN SECOND TABLE


PROC SQL NUMBER;

SELECT * 

FROM AA EXCEPT ALL

SELECT * FROM BB;

QUIT;


EXCEPT CORR :: IT WILL DISPLAY THE ROWS WHICH ARE NOT PRESENT IN 2ND TABLE


PROC SQL NUMBER;

SELECT * 

FROM AA EXCEPT CORR

SELECT * FROM BB;

QUIT;


4.INTERSECT:

-->SELECT THE UNIQUE ROWS THAT ARE  COMMON FROM BOTH TABLES


PROC SQL NUMBER;

SELECT * FROM AA

INTERSECT CORR

SELECT *

FROM BB;

QUIT;



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

--JOIN US IN TELEGRAM CHANNEL FOR MORE UPDATES

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


Comments