- Get link
- X
- Other Apps
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
- Get link
- X
- Other Apps
Comments
Post a Comment