PROC SQL过程(三)——WHERE和HAVING语句&子查询

目录

WHERE语句与HAVING语句的区别：

WHERE子句：

HAVING语句

子查询

---

WHERE语句与HAVING语句的区别：

1. WHERE在SELECT之前执行
   HAVING在SELECT和GROUP BY之后执行

2. HAVING语句作用于组
   WHERE语句作用于行

主要表现如下：

• HAVING在GROUP BY后使用，对GROUP BY的结果进行筛选。
• HAVING在使用SELECT中新创建的变量时，不需要使用CALCULATED关键字。

WHERE子句：

功能：过滤数据

注意：

1. 语句中可以使用SELECT没有指定的列，但是不能直接使用SELECT中的别名和新创建列
2. 必须写在SELECT和FROM后

3. 在SELECT语句前执行（所以不能直接使用SELECT中的别名和新创建列

关键字：CALCULATED
            用于where语句，可以使用select中的别名和新创建列。
            用于select中，使用计算变量生成新变量。

PROC SQL OUTOBS=10;
    SELECT flightnumber,date,destination,
           boarded + transferred + nonrevenue AS total
    FROM sasuser.marchflights
    WHERE CALCULATED total < 100;
QUIT;
PROC SQL OUTOBS=10;
    SELECT flightnumber,date,destination,
           boarded + transferred + nonrevenue AS total,
           CALCULATED total/2 AS half
    FROM sasuser.marchflights;
QUIT;

常用表达式：

• 条件运算符
      比较运算符：=
      逻辑运算符：or
      连接运算符：||
• 定义区间
      where salary between 70000 and 80000;     //[70000,80000]

• 是否包含
  where name contains 'ER'
  where name ? 'ER'

• 是否在...之内（与其中一个相等）
  where code in ('PT' , 'NA', 'FA')

• 是否为缺失值
  where dateofbirth is missing
  where dateofbirth is null

• 通配符
  where address like '% P%PLACE'；
  _：一个字符或数字
  %：多个字符或数字或没有
  区分大小写

• 用声音匹配
  where lastname=* 'Smith'//听起来像后面的Smith

• NOT
  在变量后面加上NOT，表示不满足此条件。

HAVING语句

功能：过滤汇总表中的数据组

注意：

• HAVING子句与至少一个汇总函数和GROUP BY子句一起使用

• 与where语句不同，可以直接使用在select中的计算变量和新建变量

PROC SQL;
    SELECT empid,salary,
           (salary/sum(salary)) AS Percent format=percent10.2
        FROM sasuser.payrollmaster
        GROUP BY jobcode
        HAVING jobcode contains 'NA';
QUIT;

PROC SQL;
    SELECT empid,salary,
           (salary/sum(salary)) AS Percent format=percent10.2
        FROM sasuser.payrollmaster
        HAVING jobcode contains 'NA';
QUIT;

子查询

查询语句中包含查询语句,执行时先执行子查询，后执行外部查询

注意：

1. 子查询也称为嵌套查询或内部查询。
2. 根据包含子查询的子句，子查询可以返回一个值或多个值。
3. 子查询最常用于WHERE和HAVING子句。
4. 子查询用括号括起来。
5. 子查询引用的表可以与外部查询引用的表相同，也可以不同。

根据子查询与原查询是否有关，可分为

• 非相关子查询
• 相关子查询
   

EXISTS：子查询至少返回一行

NOT EXISTS ：子查询返回no数据

 

返回单一值的非相关子查询

PROC SQL;
    SELECT jobcode,
           avg(salary) AS avgsalary format=dollar11.2
        FROM sasuser.payrollmaster
        GROUP BY jobcode
        HAVING avgsalary>
            (SELECT avg(salary) 
                FROM sasuser.payrollmaster);
QUIT;

返回多个值的非相关子查询：

• IN
• ANY、ALL：
• EXISTS

PROC SQL;
    SELECT empid,jobcode,dateofbirth
        FROM sasuser.payrollmaster
        WHERE jobcode in ('FA1','FA2')
            AND dateofbirth < ANY          /*这种方法会产生大量运算，不推荐，建议使用MIN和MAX*/
                (SELECT dateofbirth
                    FROM sasuser.payrollmaster
                    WHERE jobcode='FA3');
QUIT;

相关子查询：

 

PROC SQL;
    SELECT lastname,firstname
        FROM sasuser.staffmaster
        WHERE 'NA'=
            (SELECT jobcategory
                FROM sasuser.supervisors
                WHERE staffmaster.empid=supervisors.empid);
RUN;

PROC SQL;
    CREATE TABLE dst1 AS
        SELECT a.*,b.jobcategory
            FROM sasuser.supervisors b,sasuser.staffmaster a
            WHERE a.empid=b.empid;
    CREATE TABLE dst2 AS
        SELECT lastname,firstname
            FROM dst1
            WHERE jobcategory='NA';
RUN;

PROC SQL;
    SELECT lastname,firstname
        FROM sasuser.flightattendants
        WHERE not EXISTS
            (SELECT *
                FROM sasuser.flightschedule
                WHERE flightattendants.empid=
                    flightschedule.empid);
RUN;