/* Jump to: Chapter 4 Table of Contents Chapter 6 */
/*
*/
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE classgirls AS
SELECT *
FROM sashelp.class(RENAME=(name=FName) )
WHERE sex='F'
;
QUIT;
DATA moregirls;
INPUT FName $ Age;
CARDS;
Susan 16
Jane 12
Abigail 13
Zelda 16
;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
DATA cntl;
LENGTH label $ 1;
SET classgirls(RENAME=(fname=start)) end=last;
RETAIN label 'Y'
fmtname 'ClassGirls'
type 'c';
OUTPUT;
IF LAST THEN DO;
hlo='O';
label=' ';
OUTPUT;
END;
RUN;
PROC FORMAT LIBRARY=work CNTLIN=cntl;
RUN;
DATA already;
SET moregirls;
WHERE PUT(fname,$classgirls.)='Y';
RUN;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE EXISTS
( SELECT *
FROM classgirls
WHERE moregirls.fname=classgirls.fname
)
;
QUIT;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE EXISTS
( SELECT 'Hello World'
FROM classgirls
WHERE moregirls.fname=classgirls.fname
)
;
QUIT;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE fname IN
( SELECT fname
FROM classgirls
)
;
QUIT;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE fname IN
( 'Alice ' , 'Barbara' , 'Carol ' ,
'Jane ' , 'Janet ' , 'Joyce ' ,
'Judy ' , 'Louise ' , 'Mary '
)
;
QUIT;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE fname = ANY
( SELECT fname
FROM classgirls
)
;
QUIT;
PROC SQL;
SELECT *
FROM moregirls
WHERE moregirls.fname > ANY
( SELECT fname
FROM classgirls
)
;
QUIT;
PROC SQL;
CREATE TABLE already AS
SELECT *
FROM moregirls
WHERE not( fname NE ALL
( SELECT fname
FROM classgirls
)
)
;
QUIT;
PROC SQL;
SELECT *
FROM moregirls
WHERE moregirls.fname > ALL
( SELECT fname
FROM classgirls
)
;
QUIT;
PROC SQL;
SELECT *
FROM moregirls
ORDER BY fname IN
( SELECT fname
FROM classgirls
),
fname
;
QUIT;
PROC SQL;
CREATE TABLE behindscenes AS
SELECT fname IN
( SELECT fname
FROM classgirls
),
*
FROM moregirls
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
*
SELECT fname,
age,
( SELECT fname
FROM classgirls
WHERE moregirls.age = classgirls.age
AND moregirls.fname ^= classgirls.fname
) AS SameAge
FROM moregirls
;
QUIT;
PROC SQL;
SELECT fname,
age,
( SELECT COUNT(*)
FROM classgirls
WHERE moregirls.age = classgirls.age
AND moregirls.fname ^= classgirls.fname
GROUP BY classgirls.age
) AS SameAge
FROM moregirls
;
QUIT;
/* Jump to: Top of Section Top of Chapter */