/* Jump to: Chapter 5 Table of Contents Chapter 7 */
/*
*/
/* Jump to: Next Section */
DATA a;
Aa = 1;
RUN;
DATA b;
Bb = 2;
RUN;
PROC SQL;
SELECT *
FROM a CROSS JOIN b
;
QUIT;
PROC SQL;
( SELECT *
FROM a )
UNION
( SELECT *
FROM b )
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE one AS
SELECT name as FName, weight, age
FROM sashelp.class
WHERE age<13 and LENGTH(name) GE 5
ORDER BY age, RANUNI(1)
;
CREATE TABLE two AS
SELECT name as FName, age, height
FROM sashelp.class
WHERE age<13 and LENGTH(name) LE 5
ORDER BY age, RANUNI(2)
;
QUIT;
PROC SQL;
SELECT fname, age
FROM one
UNION
SELECT fname, age
FROM two
;
QUIT;
PROC SQL;
SELECT fname, age
FROM one
INTERSECT
SELECT fname, age
FROM two
;
QUIT;
PROC SQL;
SELECT fname, age
FROM one
EXCEPT
SELECT fname, age
FROM two
;
QUIT;
PROC SQL;
SELECT fname, age
FROM one
OUTER UNION
SELECT fname, age
FROM two
;
QUIT;
PROC SQL;
CREATE TABLE outer_union AS
SELECT fname, age
FROM one
OUTER UNION
SELECT fname, age
FROM two
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA concat;
SET one
two
;
RUN;
PROC SQL;
CREATE TABLE concat AS
SELECT *
FROM one
OUTER UNION CORRESPONDING
SELECT *
FROM two
;
QUIT;
DATA interleave;
SET one
two
;
BY age;
RUN;
PROC SQL;
CREATE TABLE interleave AS
SELECT *
FROM one
OUTER UNION CORRESPONDING
SELECT *
FROM two
ORDER BY age
;
QUIT;
PROC SQL;
SELECT *, 1 AS Suborder
FROM one
;
QUIT;
PROC SQL;
SELECT *, 2 AS Suborder
FROM two
;
QUIT;
PROC SQL;
CREATE TABLE interleave(DROP=suborder) AS
SELECT *, 1 AS suborder
FROM one
OUTER UNION CORRESPONDING
SELECT *, 2 AS suborder
FROM two
ORDER BY age, suborder
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA num;
ProblemVar = 123;
RUN;
DATA char;
ProblemVar = 'abc';
RUN;
/*
DATA both;
SET num char;
RUN;
*/
PROC SQL;
*
CREATE TABLE both AS
SELECT *
FROM num
OUTER UNION CORRESPONDING
SELECT *
FROM char
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE unionallcorr AS
SELECT *
FROM one
UNION ALL CORRESPONDING
SELECT *
FROM two
;
QUIT;
PROC SQL;
CREATE TABLE unionall AS
SELECT *
FROM one
UNION ALL
SELECT *
FROM two
;
QUIT;
PROC SQL;
CREATE TABLE unionall AS
SELECT fname, age
FROM one
UNION ALL
SELECT fname, age, height
FROM two
;
QUIT;
DATA ABC;
RETAIN ID 1;
DO CODE = 'aa','aa',
'bb','bb','bb','bb',
'cc','cc';
OUTPUT;
END;
RUN;
DATA ab;
RETAIN ID 1;
DO CODE = 'aa','aa','aa',
'bb','bb';
OUTPUT;
END;
RUN;
PROC SQL;
CREATE TABLE unionall AS
SELECT *
FROM abc
UNION ALL
SELECT *
FROM ab
;
QUIT;
PROC SQL;
CREATE TABLE union AS
SELECT *
FROM abc
UNION
SELECT *
FROM ab
;
QUIT;
DATA unionall;
SET abc ab;
RUN;
PROC SORT DATA=unionall OUT=union NODUPRECS;
BY _ALL_;
RUN;
DATA union;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF FIRST.code and (in_abc or in_ab);
RUN;
DATA unionall;
in_abc = 0;
in_ab = 0;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF in_abc THEN OUTPUT;
IF in_ab THEN OUTPUT;
RUN;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE intersectall AS
SELECT *
FROM abc
INTERSECT ALL
SELECT *
FROM ab
;
QUIT;
DATA intersectall;
in_abc = 0;
in_ab = 0;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF in_abc AND in_ab;
RUN;
PROC SQL;
CREATE TABLE intersect AS
SELECT *
FROM abc
INTERSECT
SELECT *
FROM ab
;
QUIT;
DATA intersect;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF FIRST.code AND in_abc AND in_ab;
RUN;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE exceptall AS
SELECT *
FROM abc
EXCEPT ALL
SELECT *
FROM ab
;
QUIT;
DATA exceptall;
in_abc = 0;
in_ab = 0;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF in_abc AND NOT in_ab;
RUN;
PROC SQL;
CREATE TABLE except AS
SELECT *
FROM abc
EXCEPT
SELECT *
FROM ab
;
QUIT;
DATA except;
MERGE abc(IN=in_abc) ab(IN=in_ab);
BY id code;
IF FIRST.code AND in_abc AND NOT in_ab;
RUN;
PROC SQL;
SELECT *
FROM ab
EXCEPT ALL
SELECT *
FROM abc
;
QUIT;
/* Jump to: Top of Section Top of Chapter */