您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
这篇文章给大家分享的是有关表链接proc sql的示例分析的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
/*21.1.1 简单连接*/ proc sql; select * from resdat.china, resdat.usa; quit /*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; /*21.1.9.2 包含所有行的Union连接 */ 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; /* 21.2.1 所有行匹配无重复值的情况 */ data merge1; merge a b; by code; run; proc print data=merge1 noobs; title 'Table MERGE1'; run; proc sql; 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; run; proc print data=merge2 noobs; title 'Table MERGE2'; run; /* 程序二*/ 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; run; proc print data=merge3 noobs; title 'Table MERGE3'; run; /* 程序二*/ 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'd; 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'd; 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的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。