t03_SAS_pSQL.sas

/* */
/* CREATE */
PROC SQL;
CREATE TABLE TCUSTR(
    CUSTR_NBR VARCHAR(18),
    SEX INT
);
QUIT;

PROC PRINT DATA = TCUSTR;
RUN;


/* */
/* INSERT */
PROC SQL;
INSERT INTO 
TCUSTR(CUSTR_NBR, SEX)
VALUES("440101200109090011", 1)
VALUES("360101199901010012", 0)
;
QUIT;

PROC PRINT DATA = TCUSTR;
RUN;


/* */
/* DELETE */
PROC SQL;
DELETE FROM TCUSTR 
WHERE CUSTR_NBR = "440101200109090011";
QUIT;

PROC PRINT DATA = TCUSTR;
RUN;


/* */
/* UPDATE */
PROC SQL;
UPDATE TCUSTR 
SET SEX = 1 
WHERE CUSTR_NBR = "360101199901010012";
QUIT;

PROC PRINT DATA = TCUSTR;
RUN;


/* */
/* SELECT */
PROC SQL;
SELECT * 
FROM SASHELP.CARS(OBS = 5);
QUIT;

PROC SQL;
SELECT * 
FROM SASHELP.CARS 
WHERE MAKE = "Acura";
QUIT;

PROC SQL;
SELECT MAKE, MSRP
FROM SASHELP.CARS 
WHERE MAKE = "Acura";
QUIT;


/* */
/* SQL FUNC */
PROC SQL;
SELECT 
    COUNT(MSRP),
    SUM(MSRP),
    MAX(MSRP),
    MIN(MSRP),
    AVG(MSRP)
FROM SASHELP.CARS 
WHERE MAKE = "Acura";
QUIT;


/* */
PROC SQL;
SELECT 
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP
FROM SASHELP.CARS 
WHERE MAKE = "Acura";
QUIT;


/* */
/* GROUP BY */
PROC SQL;
SELECT 
    MAKE,
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP 
FROM SASHELP.CARS(OBS=100)
GROUP BY MAKE;
QUIT;


/* */
/* ORDER BY */
PROC SQL;
SELECT 
    MAKE,
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP 
FROM SASHELP.CARS(OBS=100)
GROUP BY MAKE
ORDER BY CNT_MSRP;
QUIT;


/* */
/* WHERE */
PROC SQL;
SELECT 
    MAKE,
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP 
FROM SASHELP.CARS(OBS=100)
WHERE MAKE ^= "Acura"
GROUP BY MAKE
ORDER BY CNT_MSRP;
QUIT;


/* */
/* HAVING */
PROC SQL;
SELECT 
    MAKE,
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP 
FROM SASHELP.CARS(OBS=100) 
WHERE MAKE ^= "Acura"
GROUP BY MAKE
HAVING CNT_MSRP > 5
ORDER BY CNT_MSRP;
QUIT;


/* */
/* CREATE TABLE */
PROC SQL;
CREATE TABLE CARS_GROUP1 AS 
SELECT 
    MAKE,
    COUNT(MSRP) AS CNT_MSRP,
    SUM(MSRP) AS SUM_MSRP,
    MAX(MSRP) AS MAX_MSRP,
    MIN(MSRP) AS MIN_MSRP,
    AVG(MSRP) AS AVG_MSRP 
FROM SASHELP.CARS 
WHERE MAKE ^= "Acura"
GROUP BY MAKE
HAVING CNT_MSRP > 5
ORDER BY CNT_MSRP;
QUIT;

PROC PRINT DATA = CARS_GROUP1(OBS=10);
RUN;


/* */
/* CASE WHEN */
PROC SQL;
CREATE TABLE CARS_GROUP2 AS 
SELECT 
    MAKE,
    SUM(CASE WHEN ENGINESIZE > 3 THEN MSRP ELSE 0 END) AS SUM_MSRP
FROM SASHELP.CARS 
GROUP BY MAKE;
QUIT;

PROC PRINT DATA = CARS_GROUP2(OBS=10);
RUN;