SAS读书笔记：SQL

NASAC

1.SQL语言概述

SQl语言在SAS中通过PROC SQL来实现。PROC SQL可实现以下功能：

制作报表与表；
生成一些统计性数据；
合并表；
从其它表中抽取部分行、列；
更新表的行、列；
从其它数据管理系统（DBMS）中更新或抽取数据。

2.使用SQL检索数据

2.1 SQL的基本结构

PROC SQL;
    SELECT 表1.列1 , 表1.列2 ,```
    FROM 表1
    <WHERE>
    <GROUP BY>
    <ORDER BY>;
QUIT;

2.2 使用SQL对列进行操作

从句SELECT选择所需要的列，语法如下：

SELECT 表名称.列1 , 表名称.列2 ，···

在不混淆的情况下，表名称可以省略。SELECT从句中，表名称前不加库名，库名由FROM指定。

使用AS对列重命名，也可以重新定义属性，语法如下：

SELECT 表名称.列1 AS 新名称 FORMAT=  LABEL= ，表名称.列2 ···

还可以生成新的列，如下生成新列tax：

PROC SQL;
TITLE "Generating a new column";
select cars.Make,cars.Model,cars.MSRP,cars.msrp*0.06 as tax 
from sashelp.cars;
quit;

注意SELECT从句中列的先后顺序决定了输出报表中他们的输出顺序

2.3 使用SQL对行进行操作

1.DISTINCT关键字

不输出重复的行，紧跟在SELECT之后，一个SELECT从句只能有一个。

SELECT DISTINCT 表名称.列1 , 表名称.列2 ，···

2.WHERE从句

proc sql;
select make ,model,msrp,msrp*0.06 as Tax
    from sashelp.cars
where msrp<=40000;
quit;

如果针对新生成的列，则需要使用calulated

proc sql;
select make ,model,msrp,msrp*0.06 as Tax
    from sashelp.cars
where calculated t ax<=2400;
quit;

3.ORDER从句

按照某些列进行排序。语法：

ORDER BY 列1<DESC>,列2<DESC>

例如以下例子按照MSRP升序，MAKE降序，Model升序

PROC SQL；
Select make,model,msrp,msrp*0.06 as tax
  from sashelp.cars
    where calculated tax<2400
      order by msrp,make desc,model;
quit;

4.Group BY从句

用来查看分组信息。一般和汇总函数（summary function）一起使用。

我们重点关注count函数。

语法：

count (列名)

注意列名为*时，返回表中行数。
列名前加distinct，重复行只会被计算一次

例如，使用SAS计算SAShelp.cars中不同厂商的个数。

proc sql;
select count(distinct make) as numbers_of_makers
  from sashelp.cars 
quit;

计算sashelp.cars中不同厂商与他们汽车车型组合的数量,使用cats函数将make和model进行组合再计数。

proc sql;
select count(distinct cats(make,model)) as numbers_of_modle
 from sashelp.cars;
quit;

例子：对sashelp.cars中每个厂商的所有车型均价进行升序排序

proc sql;
select make Avg(msrp) as avergae_msrp
 from sashelp.cars
  group by make
order by calculated  avergae_msrp;
quit;

5.Having从句

与where从句类似，用来选择满足特定条件的行。
与where区别：where操作在Select之前，Having在select和group by 之后，因此涉及Group By时只能用Having从句。没有Group by时，having可以替代select。由于having 从句在Select之后执行，所以对于select从句中新生成的列，无需添加calcuted关键字

例子：计算sashelp.cars中每个厂商的所有车型均价，输出小于20000的行，结果按照厂商字母排序。

proc sql;
select make,avg(msrp) as average_price
from sashelp.cars
 group by make
  having average_price <20000
   order by make;
quit;

2.4 使用SQL对报表加工与生成数据集

SQL提供一些选项来对报表进行加工，同时允许用户将报表存成数据集。

1.number和nonumber选项

默认SQL输出报表不输出行数（proc print 输出行数）可以使用number看到行数。语法：

proc sql number;

2.OUTOBS和INOBS选项

outobs=选项允许用户输出前若干行。

outobs=n

inobs控制读入表的行数

inobs=n

例子：

proc  sql outobs=10 number;
title 'first ten car model in the list';
select distinct make ,model
from sashelp.cars;
quit;

使用create将生成的数据存到数据集

create table 库名.表名 as

例子

proc  sql outobs=10 number;
title 'first ten car model in the list';
create table work.list_ten_model as
select distinct make ,model
from sashelp.cars;
quit;

2.5 子查询

即在查询中内嵌一个查询，分为不相关子查询、相关子查询。

不相关子查询：子查询与原查询无关
相关子查询：子查询与原查询有关

例子：生成一个报表，仅包含平均零售价不超过Max_expending均值的汽车制造商

表spending，包含被调查人用于购买汽车的最大支出;

ID Max_expending
1 16000
2 20000
3 24000
4 18000
5 23000

data work.spending;
input ID Max_expending;
datalines;
1 16000
2 20000
3 24000
4 18000
5 23000
proc sql;
title 'recomend brands';
select cars.make,avg(cars.msrp) as average_price
  from sashelp.cars
    group by make
     having average_price<=
(select avg(Max_expending) from work.spending);
quit;

上述代码中，在Having从句中用到了子查询(select avg(Max_expending) from work.spending)，proc sql 会优先处理子查询，再返回到原查询语句中处理。上例为不相关查询。

3.使用SQL对表进行横向合并

3.1 使用SQL对表进行内连接（inner joins）

内链接 ：在对表进行横向连接的时候，根据连接的条件，仅返回两个表中所有匹配的数据。

卡氏积 ：内链接的特殊形式。表A中的行和表B中的行的所有可能的组合。

内连接时常见的用于输出满足条件的部分卡氏积表观测的一种方法。语法如下：

PROC SQL;
SELECT 表1.列1,表1.列2,···表2.列1,表2.列2,···
FROM 表1,表2
WHERE 从句
QUIT；

上述从句执行顺序：

根据FROM从句中的表生成卡氏积。
根据WHERE从句选择卡氏积中的每一行进行扫描，判断是否符合条件，删除不满足条件的行。
若SQLc从句中有汇总函数，则根据汇总函数进行相应的处理，否则进入下一步。
输出满足条件的行。

使用PROC SQL对两表进行横向合并时，总是先生成卡氏积，再进行判断。优点：不要求表已经排序。缺点：会生成更大的卡氏积表。

例子：

data work.class;
input name $ sex $ age height;
datalines;
Alice F 14 56.5
Carol F 14 62.8
James M 12 57.3
data work.classfit;
input student_name $ weight;
datalines;
James 83
Carol 102.5
proc sql;
select class.name,class.sex,class.age,class.height,classfit.weight
 from work.class,work.classfit
  where class.name=classfit.student_name;
 quit;

3.2 使用SQL对表进行外连接（outer joins）

外连接：输出两表内链接的行以及部分来自表A或者表B的行。有以下三种：

左连接（left join）
右连接（right join）
全连接（full join）

语法如下：

PROC SQL;
SELECT 表1.列1,表1.列2,···表2.列1,表2.列2,···
FROM 表1
left join|right join|full join
on 从句
QUIT；

其中连接条件的关键词是on

例子：

左连接

data work.A;
input x value1 $;
datalines;
data work.B;
input x value2 $;
datalines;
proc sql;
select *
 from work.A
left join|right join|full join
work.B
  on A.x=B.x;
 quit;

4.使用SQL对表进行纵向合并

语法如下：

proc sql;
  select * from A
  连接方式<all><corr>
  select * from B
  ;

其它从句中u与单表操作一样，可以使用where ，group by，having
连接方式有except、intersect、union、outerunion

4.1 EXCEPT

SQL会选择在表A中但不在表B中的行，并且A中重复的行不会出现在合并中。

proc sql;
title 'Combine Two Tables Vertically using Except';
  select * from A
except
  select * from B;
quit;

合并后表的列名称与第一个表一致。合并时要求两表对应列的类型必须一致，否则报错。
PROC SQL会进行两轮扫描，扫描前一次，扫描后一次。
第一轮扫描，扫描A中重复的行，进行删除。
第二轮扫描，关键字EXCEPT的作用是使SQL仅输出在表A，不在表B的行。

关键字ALL

SQL不再进行第一次扫描，保留A中重复的行。

关键字CORR

SQL会根据表中列的名称进行合并，保留相同名称列。不要求列的位置相同。

4.2 INTERSECT

istersect 删除A中重复的行，输出剩余行与B的交集

intersect all 不删除A中重复的行，直接输出与B的交集

intersect corr 基于列的名称合并

proc sql;
select * from work.A
intersect
select * from work.B;
quit;

proc sql;
select * from work.A
intersect all
select * from work.B;
quit;

4.3 UNION

基于列的位置进行纵向合并。

union SQL首先会对表进行纵向合并排序，接着删除重复的行，

union all 没有删除重复的行，也没有进行排序

union corr 根据表的名称来合并列，输出结果仅包含共同列

4.4 OUTER UNION

不会覆盖表的列，两个表的所有行都会出现在输出结果中。

不能和 all一起使用

union corr 会合并同名列

5.使用SQL管理表

DESCRIBE 语句，输出表的结构：表的列、属性、长度和标签。

proc sql;
describe table 表名;
quit;

5.1 复制、创建、删除表

1、复制表

之前学习的set语句可以实现。

本章用proc sql来实现：

proc sql；
create table work.cars_copy as
 select * from sashelp.cars;
quit;

2、创建空表

创建一个空表，表中属性与某表格一致。

proc sql;
create table work.new_class
like sashelp.class;
quit;

3、删除表

proc sql;
drop table 表名;
quit;

5.2 插入行

INSERT方法有以下三种

set从句
value从句
使用查询的结果

过程：SAS现在表中插入一个新行，然后再进行复制。

set

proc sql;
insert into new_table
set name=''
     age=''
set name=''
     age='';
quit;

value

proc sql;
insert into new_table (value1,value2,value n) #变量名称
               values('','', ''）   #变量值
               values('','','')     #变量值
quit;

5.3 删除部分行

delete

delete from tableName
where

5.4 修改表的列

Alter Table

proc sql;
alter table 表名；
ADD 列1，列2···
DROP 列1，列2···
MODIFY 列1，列2···

以上ADD，DROP，MODIFY分别用来新增，删减，修改。

5.5 使用SQL更新列的值

多个UPDATE语句

proc sql;
create table class as
select * from sashelp.class;
quit;
proc sql;
update class
    set height=height*1.05,weight=weight*1.05
    where age in (11,12);
quit;

UPDATE和CASE

proc sql;
update class
    set height=height*