共计 4235 个字符，预计需要花费 11 分钟才能阅读完成。
这篇文章给大家分享的是有关表链接 proc sql 的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
/*21.1.1 简单连接 */
proc sql;
select * from resdat.china, resdat.usa;
/*21.1.3 内部连接 */
proc sql;
select * from resdat.china, resdat.usa
where china.level=usa.level;
quit;
/*21.1.3.1 使用表的别名 */
proc sql;
select * from resdat.china as a, resdat.usa as b
where a.level=b.level;
quit;
/*21.1.3.2 使用别名进行表的自我连接 */
proc sql;
select * from resdat.china a, resdat.china b
where a.level b.level;
quit;
/*21.1.3.3 设定连接输出的排列顺序 */
proc sql;
select * from resdat.china a, resdat.china b
where a.level b.level
order by a.level desc;
quit;
/*21.1.6 缺失值对连接的影响 */
/* 程序一 */
proc sql;
title Table A and B Joined
select a.obs A-OBS , a.stkcd, b.obs B-OBS , b.stkcd
from a, b
where a.stkcd= b.stkcd;
/* 程序二 */
proc sql;
title Table Three and Four Joined
select Three.Obs 3-OBS , Three.Fdcd, Four.Obs 4-OBS , Four.Fdcd
from Three, Four
where Three.fdcd= Four.fdcd and
three.fdcd is not missing;
/* 21.1.7 从多于两个表的数据集中查询数据 */
proc sql outobs=3;
select a.stkcd,b.lstknm,c.clpr
from resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist c
where a.stkcd=b.stkcd and b.stkcd=c.stkcd and a.stkcd=c.stkcd;
quit;
/* 21.1.8.1 左外部连接 */
proc sql;
select * from resdat.china a left join resdat.usa b
on a.level=b.level;
quit;
/* 21.1.8.2 右外部连接 */
proc sql;
select * from resdat.china a right join resdat.usa b
on a.level=b.level;
quit;
/*21.1.8.3 完全外部连接 */
proc sql;
select * from resdat.china a full join resdat.usa b
on a.level=b.level;
quit;
/*21.1.9.1 与简单连接功能相同的 Cross 连接 */
proc sql;
select * from resdat.china cross join resdat.usa;
quit;
proc sql;
select * from resdat.china union join resdat.usa;
quit;
/*21.1.9.3 使用自动匹配连接的 Natural 连接 */
proc sql;
select * from resdat.china union join resdat.usa;
quit;
/* 21.1.10 连接使用 COALESCE 函数 */
Proc sql;
select a.level,a.china,coalesce(b.level,a.level),coalesce(b.usa,a.china)as usa
from resdat.china a full join resdat.usa b
on a.level=b.level;
quit;
title Table MERGE1
select a.code, a.manager, b.Assitant
from a, b
where a.code=b.code;
quit;
/* 21.2.2 部分行匹配无重复值的情况 */
/* 程序一 */
data merge2;
merge a b;
by code;
proc print data=merge2 noobs;
title Table MERGE2
/* 程序二 */
proc sql;
select code,a.manager,b.assistant
from a natural full join b;
quit;
/* 21.2.3 有重复值的情况 */
/* 程序一 */
data merge3;
merge a b;
by code;
proc print data=merge3 noobs;
title Table MERGE3
/* 程序二 */
Proc sql;
Title Table Merge3
Select a.code, a.manager, b.assistant
From a full join b
On a.code=b.code;
quit;
/* 21.3.1 产生单个值的子查询 */
Proc sql;
Title Which Manager has the same code as Assistant Chen
Select *
From a
Where code eq (select code from b where assistant= Chen
Quit；/* 21.3.2 产生多个值的子查询 */
Proc sql;
select stkcd,lstknm,lstdt from resdat.lstkinfo
where stkcd in (select stkcd from resdat.sampstk);
quit;
/* 21.3.3 混合子查询 */
proc sql;
select stkcd,yrret from resdat.yrret a
where (select stktype from resdat.lstkinfo b
where a.stkcd=b.stkcd)= A
and 1jan2005 d =date = 31dec2005
quit;
/* 21.3.5 子查询的多重嵌套 */
Proc sql;
select stkcd,yrret from resdat.yrret a
where stkcd in (select stkcd from resdat.sampstk b
where stkcd in(select stkcd from resdat.lstkinfo c
where c.stktype= A ))
and 1jan2005 d =date = 31dec2005
quit;
/*21.3.6 在 JOIN 连接中使用子查询 */
proc sql;
select a.id,b.id,sqrt((a.x-b.x)**2+(a.y-b.y)**2)as dist from point a,point b
where a.id lt b.id and
calculated dist=(select min(sqrt((c.x-d.x)**2+(c.y-d.y)**2))
from point c,point d
where c.id lt d.id);
quit;
/*21.5.2 由多个查询产生非重复观测 (UNION 算符)*/
/* 程序一 */
proc sql;
title A UNION B
select * from A
union
select * from B;
quit;
/* 程序二 */
proc sql;
title A UNION ALL B
select * from A
union all
select * from B;
quit;
/*21.5.3 产生只属于第一个查询的观测 (EXCEPT 算符)*/
/* 程序一 */
proc sql;
title A EXCEPT B
select * from A
except
select * from B;
quit;
/* 程序二 */
proc sql;
title A EXCEPT ALL B
select * from A
except all
select * from B;
/*21.5.4 从多个查询中产生公共部分 (INTERSECT 算符)*/
proc sql;
title A INTERSECT B
select * from A
intersect
select * from B;
/*21.5.5 直接连接查询结果 (OUTER UNION 算符)*/
/* 程序一 */
proc sql;
title A OUTER UNION B
select * from A
outer union
select * from B;
/* 程序二 */
proc sql;
title A OUTER UNION CORR B
select * from A
outer union corr
select * from B;
/* 21.5.6 特殊的查询合并方式 */
proc sql;
title A EXCLUSIVE UNION B
(select * from A
except
select * from B)
union
(select * from B
except
select * from A);感谢各位的阅读！关于“表链接 proc sql 的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
正文完
