/* Jump to: Chapter 3 Table of Contents Chapter 5 */
/*
*/
/* Jump to: Next Section */
PROC SQL;
SELECT name
FROM sashelp.class
WHERE name='Jane'
;
QUIT;
PROC SQL;
SELECT class.name
FROM sashelp.class
WHERE class.name='Jane'
;
QUIT;
PROC SQL;
*
SELECT sashelp.class.name
FROM sashelp.class
WHERE sashelp.class.name='Jane'
;
QUIT;
PROC SQL;
SELECT s_h_c.name
FROM sashelp.class AS s_h_c
WHERE s_h_c.name='Jane'
;
QUIT;
PROC SQL;
SELECT LOWCASE(name) AS name
FROM sashelp.class
WHERE name='jane';
;
QUIT;
PROC SQL;
*
SELECT LOWCASE(name) AS lowname
FROM sashelp.class
WHERE lowname='jane';
;
QUIT;
PROC SQL;
SELECT LOWCASE(name) AS name
FROM sashelp.class
WHERE CALCULATED name='jane';
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA one;
DO Value1 = 11,12;
OUTPUT;
END;
RUN;
DATA two;
DO Value2 = 21,22,23;
OUTPUT;
END;
RUN;
DATA combined;
MERGE one two;
RUN;
PROC SQL;
CREATE TABLE combined AS
SELECT *
FROM one CROSS JOIN two
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA u1;
INPUT Key $ Value1;
CARDS;
A 11
B 12
;
DATA u2;
INPUT Key $ Value2;
CARDS;
C 23
A 21
;
PROC SORT DATA=u1 OUT=sorted1;
BY key;
RUN;
PROC SORT DATA=u2 OUT=sorted2;
BY key;
RUN;
DATA combined;
MERGE sorted1 sorted2;
BY key;
RUN;
PROC SQL;
SELECT *
FROM u1 FULL JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
SELECT COALESCE(u1.key , u2.key) AS Key,
*
FROM u1 FULL JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
CREATE TABLE combined AS
SELECT COALESCE(u1.key , u2.key) AS Key,
*
FROM u1 FULL JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
CREATE TABLE combined AS
SELECT COALESCE(u1.key , u2.key) AS Key,
value1,
value2
FROM u1 FULL JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
SELECT *
FROM u1 LEFT JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
CREATE TABLE combined_left AS
SELECT u1.*,
value2
FROM u1 LEFT JOIN u2
ON u1.key = u2.key
;
QUIT;
DATA combined_left;
MERGE sorted1(IN=in1) sorted2;
BY key;
IF in1;
RUN;
DATA combined_right;
MERGE sorted1 sorted2(IN=in2);
BY key;
IF in2;
RUN;
PROC SQL;
CREATE TABLE combined_right AS
SELECT u2.key,
value1,
value2
FROM u1 RIGHT JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
SELECT *
FROM u1 INNER JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
CREATE TABLE combined_inner AS
SELECT u1.*,
value2
FROM u1 INNER JOIN u2
ON u1.key = u2.key
;
QUIT;
DATA combined_inner;
MERGE sorted1(IN=in1) sorted2(IN=in2);
BY key;
IF in1 AND in2;
RUN;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA m1;
INPUT Key $ Value1;
CARDS;
A 11.1
A 11.2
B 12.1
B 12.2
;
DATA m2;
INPUT Key $ Value2;
CARDS;
A 21.1
A 21.2
A 21.3
C 23.1
C 23.2
;
DATA many_inner;
MERGE m1(IN=in1) m2(IN=in2);
BY key;
IF in1 and in2;
RUN;
PROC SQL;
SELECT *
FROM m1 INNER JOIN m2
ON m1.key = m2.key
;
QUIT;
PROC SQL;
CREATE TABLE many_inner AS
SELECT m1.*,
value2
FROM m1 INNER JOIN m2
ON m1.key = m2.key
;
QUIT;
DATA one_many_inner;
MERGE u1(IN=in1) m2(IN=in2);
BY key;
IF in1 AND in2;
RUN;
PROC SQL;
CREATE TABLE one_many_inner AS
SELECT u1.*,
value2
FROM u1 INNER JOIN m2
ON u1.key = m2.key
;
QUIT;
PROC SQL;
CREATE TABLE one_many_outer AS
SELECT coalesce(u1.key, m2.key) AS Key,
value1,
value2
FROM u1 FULL JOIN m2
ON u1.key = m2.key
;
QUIT;
DATA one_many_outer;
MERGE u1 m2;
BY key;
RUN;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA from3;
MERGE sorted1 m1(RENAME=(value1=Tenths) ) sorted2;
BY key;
RUN;
PROC SQL;
CREATE TABLE sql_from3 AS
SELECT u1.*,
m1.value1 as Tenths,
u2.value2
FROM (u1 JOIN m1 ON u1.key=m1.key)
JOIN u2 ON u1.key=u2.key
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
SELECT COALESCE(u1.key , u2.key) AS Key,
value1,
value2
FROM u1 FULL JOIN u2
ON u1.key = u2.key
;
QUIT;
PROC SQL;
SELECT *
FROM u2 NATURAL FULL JOIN u1
;
QUIT;
PROC SQL;
SELECT u1.*,
m1.value1 as Tenths,
u2.value2
FROM (u1 JOIN m1 ON u1.key=m1.key)
JOIN u2 ON u1.key=u2.key
;
QUIT;
PROC SQL;
SELECT u1.*,
m1.value1 as Tenths,
u2.value2
FROM u1, m1, u2
WHERE u1.key=m1.key AND u1.key=u2.key
;
QUIT;
/* Jump to: Top of Section Top of Chapter */