SAS中的sql语句的使用-多表使用
SAS 中的多表使用一般为多表之间的连接查询
连接查询分为：
（1）表内连接
（2）表与表之间的连接
这里我们只讨论表与表之间的连接，而表与表之间的连接分为：
（1）全连接
这里的全连接就是基本的笛卡尔积，笛卡尔积通俗一点说就是每条数据的交叉相乘，使用full join
data temp;
input visit $ visit_dat $ age type $;
cards;
v1 20190201 18 a
v2 20200304 21 f
v3 20190825 34 e
v1 20180431 58 c
v2 20170902 23 d
v4 20160826 25 r
;
run;
data temp2;
input visit $ visit_dat $ age type $;
cards;
v3 20190725 25 d
v4 20200431 35 e
v5 20190921 38 g
;
run;
/* 创建表,含条件语句 */
proc sql;
create table join_visit as
select
table1.visit as visit1,
table1.visit_dat as visit_dat1,
table1.age as age1,
table1.type as type1,
table2.visit as visit1,
table2.visit_dat as visit_dat2,
table2.age as age2,
table2.type as type2
from
work.temp as table1
full join
work.temp2 as table2
on
table1.visit = table2.visit;
run;
/* 打印创建的表 */
proc print data=join_visit;
run;
结果为：
（2）左外连接
保留左边的所有数据，对于右边的，只保留符合条件的数据，使用 left join
data temp;
input visit $ visit_dat $ age type $;
cards;
v1 20190201 18 a
v2 20200304 21 f
v3 20190825 34 e
v1 20180431 58 c
v2 20170902 23 d
v4 20160826 25 r
;
run;
data temp2;
input visit $ visit_dat $ age type $;
cards;
v3 20190725 25 d
v4 20200431 35 e
v5 20190921 38 g
;
run;
/* 创建表,含条件语句 */
proc sql;
create table join_visit as
select
table1.visit as visit1,
table1.visit_dat as visit_dat1,
table1.age as age1,
table1.type as type1,
table2.visit as visit1,
table2.visit_dat as visit_dat2,
table2.age as age2,
table2.type as type2
from
work.temp as table1
left join
work.temp2 as table2
on
table1.visit = table2.visit;
run;
/* 打印创建的表 */
proc print data=join_visit;
run;
（3）右外连接
保留右边的所有数据，对于左边的，只保留符合条件的数据，使用 right join
data temp;
input visit $ visit_dat $ age type $;
cards;
v1 20190201 18 a
v2 20200304 21 f
v3 20190825 34 e
v1 20180431 58 c
v2 20170902 23 d
v4 20160826 25 r
;
run;
data temp2;
input visit $ visit_dat $ age type $;
cards;
v3 20190725 25 d
v4 20200431 35 e
v5 20190921 38 g
;
run;
/* 创建表,含条件语句 */
proc sql;
create table join_visit as
select
table1.visit as visit1,
table1.visit_dat as visit_dat1,
table1.age as age1,
table1.type as type1,
table2.visit as visit1,
table2.visit_dat as visit_dat2,
table2.age as age2,
table2.type as type2
from
work.temp as table1
right join
work.temp2 as table2
on
table1.visit = table2.visit;
run;
/* 打印创建的表 */
proc print data=join_visit;
run;
结果为：
页:
[1]