《SAS编程与数据挖掘商业案例》学习笔记之十七

最新推荐文章于 2022-04-21 12:34:06 发布

转载最新推荐文章于 2022-04-21 12:34:06 发布 · 119 阅读

本文深入探讨SAS SQL在商业应用中的实用技巧，包括单表操作、多表关联、子查询及merge与join的区别等内容，并通过具体示例说明如何高效使用这些特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

继续读书笔记，本次重点sas sql语句，因为sql内容多且复杂，本文仅仅介绍商业应用中经常使用的而且easy出错的地方，内容包含：单表操作、多表关联、子查询以及merge和join的差别

1.单表操作

eg1：

Proc sql outobs=10。 *可选项，功能类似于data步中的obs数据集选项

create table class as

Select name,

case when sex eq 'M' then "1" when sex eq 'F' then "2" else "3"

end as sex_tran label="sextrans", *输出数据集中作为sex_trans的中文标签

sum((weight-20)) as new1 format=16.2, *sas查询特有的形式

sum((height-10)) as new2 format=16.2,

(calculated new1 - calculated new2) as new, *calculated是sas在sql中特有的语句，用于对产生的新变量再操作

From sashelp.class(where=(height between 50 and 70))

Group by name,calculated sex_tran;

eg2：

proc sql;

create table test1 as

select

id,

max (txn_seq) as txn_seq,

sum (amount) as sum_amt

from chap11.having1

group by id

having calculated sum_amt ge 70

;

quit;

注：having语句出如今group by后面，假设没哟group by语句，系统默认having语句和where语句效果同样

proc sql;

create table test2 as

select

id,

txn_seq,

amount

from chap11.having1

group by id

having txn_seq= max (txn_seq)

;

quit;

注：having语句和group by语句同一时候出现时，select后面不一定须要汇总函数如sum等，上例中。依照每个id取最大的txn_seq

proc sql;

create table test3 as

select

id,

txn_seq,

amount

from chap11.having2

group by id

having txn_seq = max (txn_seq)

;

quit;

having语句和max或min结合使用时，一定要注意having后面的变量在每个id中的唯一性。

2.多表关联

左联接在join变量是一对一的情况下，假设where在表的外面，则where条件是先关联表。后筛选数据，假设where在表中，则是先筛选数据后关联表，and也是先筛选数据后关联表。因而表内写where和表外写and是全然一样的。

下面程序。2和3全然一样，可是1和后面两个不一样

eg：

proc sql;

create table leftjoin1 as

select

case

when b.rmb_id eq . then a.id

else b.rmb_id

end as all_rmb,

a.id,

b.rmb_id,

b.usd_id

from chap11.left_join_1 as a

left join chap11.left_join_2 as b

on a.id=b.usd_id

where rmb_id le 4

;

quit;

proc sql;

create table leftjoin2 as

select

case

when b.rmb_id eq . then a.id

else b.rmb_id

end as all_rmb,

a.id,

b.rmb_id,

b.usd_id

from

chap11.left_join_1 as a

left join chap11.left_join_2

(where=(rmb_id le 4)) as b

on a.id=b.usd_id

;

quit;

proc sql;

create table leftjoin3 as

select

case

when b.rmb_id eq . then a.id

else b.rmb_id

end as all_rmb,

a.id,

b.rmb_id,

b.usd_id

from chap11.left_join_1 as a

left join chap11.left_join_2 as b

on a.id=b.usd_id

and rmb_id le 4

;

quit;

3.子查询

in子查询效率比join低非常多，而exist子查询效率更低；

4.merge和sql的比較

在“一对一”和“多对一”的情况下，效果全然同样。可是在“多对多”情况下。则全然不同。

创建測试数据集

data a1;

input x y @@;

cards;

101 1 102 3

103 30 104 5

;

run;

data a2;

input x y @@;

cards;

101 10 102 30

103 5 105 50

;

run;

data a3;

input x y z1 @@;

cards;

101 11 1 102 33 2

102 300 3 104 5 4

;

run;

data a4;

input x y z2 @@;

cards;

101 1 5 102 30 6

102 5 7 102 100 8

102 200 9 105 50 10

;

run;

eg1：求a1和a2的交集

sql实现：

merge实现

proc sql;

select

a1.x

,a2.y

from a1 join a2

on a1.x=a2.x

;

quit;

proc sort data=a1;by x;run;

proc sort data=a2;by x;run;

data a12;

merge a1(in=ina) a2(in=inb);

by x;

if ina and inb;

proc print;run;

注：sql是通过内连接实现，merge通过if ina and inb实现

eg2：用数据集a2来更新数据集a1

sql实现：

merge实现

proc sql;

select

a1.x

,case when a2.y is not null then a2.y

else a1.y end as yy

from a1

left join a2

on a1.x=a2.x

;

quit;

proc sort data=a1;by x;run;

proc sort data=a2;by x;run;

data a12;

merge a1(in=ina) a2(in=inb);

by x;

if ina;

proc print;run;

注：sql通过左联接方式实现。merge通过if ina方式实现。等价于左联接

eg3：用数据集a2来更新数据集a1，同一时候保留两个数据集全部观測

sql实现：

merge实现

proc sql;

select

coalesce(a1.x,a2.x) as x

,coalesce(a2.y,a1.y) as yy

from a1

full join a2

on a1.x=a2.x

;

quit;

proc sort data=a1;by x;run;

proc sort data=a2;by x;run;

data a12;

merge a1 a2;

by x;

run;

proc print;run;

注：sql通过全连接方式实现，需求中须要用a2全部变量更新a1，所以一定要把a2变量放在前面，被更新的数据集放在后面，可是对join的匹配变量，对这样的顺序无要求；merge没有使用in=选项，等价于全连接；

eg4：多对多

sql实现

merge实现

proc sql;

select

a3.x,a4.y

,a3.z1,a4.z2

from a3

join a4

on a3.x=a4.x

;

quit;

proc sort data=a3;by x;run;

proc sort data=a4;by x;run;

data a34;

merge a3(in=ina) a4(in=inb);

by x;

if ina and inb;

run;

proc print;run;

注：sql形式会出现反复值，匹配到的数据会进行笛卡尔。

而merge则由于if ina and inb的作用，避免了反复

5.创建表

复制已有的表属性

proc sql;

create table a

like sashelp.class;

describe table a;

quit;

6.行操作

加入行操作

使用set语句

使用value语句

使用select语句

proc sql;

insert into countries

set name='bangladesh',

capital='dhaka',

population=126391060

set name='japan',

capital='tokyo',

population=126352003;

quit;

proc sql;

insert into countries

values ('pakistan', 'islamabad', 123060000)

values ('nigeria', 'lagos', 99062000);

quit;

proc sql;

create table newconntries

like countries;

insert into newconntries

select * from countries

where population ge 120000000;

quit;

最后注意：

多表关联时。最好不要超过5个表。除非都是代码表。否则sql会产生非常大的暂时空间。从而减少程序性能

除非必须，否则优先使用关联，而不要用子查询

在使用set操作符时。假设set表没有反复行，则使用union all会提高性能

假设有索引。尽可能用索引和where语句结合

尽量避免多对多join