SAS Join(Proc sql) PK Merge（Data Step）

中午好！

今天给大家带来的是一SAS基础篇的内容！

多表的联合

之

Proc Sql Join

PK

Data Step Merge

So，PK开始

测试程序就得有数据

先制造数据，如下

proc delete data=work._all_;quit;

data ICF;

length CN $2. DN $5. ICFDAT $10.;

input CN $ DN $ ICFDAT $ ;

Cards;

01 01001 2017-11-11

01 01002 2017-11-12

01 01003 2017-11-13

01 01004 2017-11-14

01 01005 2017-11-15

01 01006 2017-11-16

;

run;

data DM;

length CN $2. DN $5. BIRTHDAT $10.;

input CN $ DN $ BIRTHDAT $ ;

Cards;

01 01001 1993-11-11

01 01002 1993-11-12

01 01007 1993-11-17

;

run;

<DM>

<ICF>

• Merge If a;

data Temp1_2;

merge ICF(in=a) DM(in=b);

by cn dn;

if a;

run;

<Sql实现方法>

proc sql ;

create table Temp1_1 as

select distinct a.*,b.BIRTHDAT from ICF as a

left join DM as b

on a.cn =b.cn

and a.dn =b.dn ;

quit;

• Merge If b;

data Temp1_2;

merge ICF(in=a) DM(in=b);

by cn dn;

if b ;

run;

<Proc Sql实现同样的效果>

proc sql ;

create table Temp1_1 as

select distinct b. *,a.*

from ICF as a

right join DM as b

on a.cn =b.cn

and a.dn =b.dn ;

quit;

• Merge if a and b;

data Temp1_2;

merge ICF(in=a) DM(in=b);

by cn dn;

if b and a ;

run;

<proc Sql实现相同的效果>

proc sql UNDO_POLICY=NONE;

create table Temp1_1 as

select distinct *

from ICF as a, DM as b

where a.cn=b.cn and a.dn=b.dn;

quit;

• Merge IF a or b;

data Temp1_2;

merge ICF(in=a) DM(in=b);

by cn dn;

if b or a ;

run;

<Proc Sql 同样的效果>

proc sql ;

create table Temp1_1 as

select distinct coalesce(a.cn,b.cn) as cn,coalesce(a.dn,b.dn) as dn,*

from ICF as a

full join DM as b

on a.cn =b.cn

and a.dn =b.dn ;

quit;

常见的Merge用法好像就这么多！

如果还有其他的，可能就是不常见的！

因为我也没见过！

虽然说我见识可能不多！

但这些差不多够用了！

Proc Sql 还有很多用法<以后有机会再说>