这篇文章给大家分享的是有关表链接proc sql的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

/*21.1.1简单连接*/procsql;select*fromresdat.china,resdat.usa;quit/*21.1.3内部连接*/procsql;select*fromresdat.china,resdat.usawherechina.level=usa.level;quit;/*21.1.3.1使用表的别名*/procsql;select*fromresdat.chinaasa,resdat.usaasbwherea.level=b.level;quit;/*21.1.3.2使用别名进行表的自我连接*/procsql;select*fromresdat.chinaa,resdat.chinabwherea.level<b.level;quit;/*21.1.3.3设定连接输出的排列顺序*/procsql;select*fromresdat.chinaa,resdat.chinabwherea.level<b.levelorderbya.leveldesc;quit;/*21.1.6缺失值对连接的影响*//*程序一*/procsql;title'TableAandBJoined';selecta.obs'A-OBS',a.stkcd,b.obs'B-OBS',b.stkcdfroma,bwherea.stkcd=b.stkcd;/*程序二*/procsql;title'TableThreeandFourJoined';selectThree.Obs'3-OBS',Three.Fdcd,Four.Obs'4-OBS',Four.FdcdfromThree,FourwhereThree.fdcd=Four.fdcdandthree.fdcdisnotmissing;/*21.1.7从多于两个表的数据集中查询数据*/procsqloutobs=3;selecta.stkcd,b.lstknm,c.clprfromresdat.sampstka,resdat.lstkinfob,resdat.qttndistcwherea.stkcd=b.stkcdandb.stkcd=c.stkcdanda.stkcd=c.stkcd;quit;/*21.1.8.1左外部连接*/procsql;select*fromresdat.chinaaleftjoinresdat.usabona.level=b.level;quit;/*21.1.8.2右外部连接*/procsql;select*fromresdat.chinaarightjoinresdat.usabona.level=b.level;quit;/*21.1.8.3完全外部连接*/procsql;select*fromresdat.chinaafulljoinresdat.usabona.level=b.level;quit;/*21.1.9.1与简单连接功能相同的Cross连接*/procsql;select*fromresdat.chinacrossjoinresdat.usa;quit;/*21.1.9.2包含所有行的Union连接*/procsql;select*fromresdat.chinaunionjoinresdat.usa;quit;/*21.1.9.3使用自动匹配连接的Natural连接*/procsql;select*fromresdat.chinaunionjoinresdat.usa;quit;/*21.1.10连接使用COALESCE函数*/Procsql;selecta.level,a.china,coalesce(b.level,a.level),coalesce(b.usa,a.china)asusafromresdat.chinaafulljoinresdat.usabona.level=b.level;quit;/*21.2.1所有行匹配无重复值的情况*/datamerge1;mergeab;bycode;run;procprintdata=merge1noobs;title'TableMERGE1';run;procsql;title'TableMERGE1';selecta.code,a.manager,b.Assitantfroma,bwherea.code=b.code;quit;/*21.2.2部分行匹配无重复值的情况*//*程序一*/datamerge2;mergeab;bycode;run;procprintdata=merge2noobs;title'TableMERGE2';run;/*程序二*/procsql;selectcode,a.manager,b.assistantfromanaturalfulljoinb;quit;/*21.2.3有重复值的情况*//*程序一*/datamerge3;mergeab;bycode;run;procprintdata=merge3noobs;title'TableMERGE3';run;/*程序二*/Procsql;Title'TableMerge3';Selecta.code,a.manager,b.assistantFromafulljoinbOna.code=b.code;quit;/*21.3.1产生单个值的子查询*/Procsql;Title'WhichManagerhasthesamecodeasAssistantChen';Select*FromaWherecodeeq(selectcodefrombwhereassistant='Chen');Quit；/*21.3.2产生多个值的子查询*/Procsql;selectstkcd,lstknm,lstdtfromresdat.lstkinfowherestkcdin(selectstkcdfromresdat.sampstk);quit;/*21.3.3混合子查询*/procsql;selectstkcd,yrretfromresdat.yrretawhere(selectstktypefromresdat.lstkinfobwherea.stkcd=b.stkcd)='A'and'1jan2005'd<=date<='31dec2005'd;quit;/*21.3.5子查询的多重嵌套*/Procsql;selectstkcd,yrretfromresdat.yrretawherestkcdin(selectstkcdfromresdat.sampstkbwherestkcdin(selectstkcdfromresdat.lstkinfocwherec.stktype='A'))and'1jan2005'd<=date<='31dec2005'd;quit;/*21.3.6在JOIN连接中使用子查询*/procsql;selecta.id,b.id,sqrt((a.x-b.x)**2+(a.y-b.y)**2)asdistfrompointa,pointbwherea.idltb.idandcalculateddist=(selectmin(sqrt((c.x-d.x)**2+(c.y-d.y)**2))frompointc,pointdwherec.idltd.id);quit;/*21.5.2由多个查询产生非重复观测(UNION算符)*//*程序一*/procsql;title'AUNIONB';select*fromAunionselect*fromB;quit;/*程序二*/procsql;title'AUNIONALLB';select*fromAunionallselect*fromB;quit;/*21.5.3产生只属于第一个查询的观测(EXCEPT算符)*//*程序一*/procsql;title'AEXCEPTB';select*fromAexceptselect*fromB;quit;/*程序二*/procsql;title'AEXCEPTALLB';select*fromAexceptallselect*fromB;/*21.5.4从多个查询中产生公共部分(INTERSECT算符)*/procsql;title'AINTERSECTB';select*fromAintersectselect*fromB;/*21.5.5直接连接查询结果(OUTERUNION算符)*//*程序一*/procsql;title'AOUTERUNIONB';select*fromAouterunionselect*fromB;/*程序二*/procsql;title'AOUTERUNIONCORRB';select*fromAouterunioncorrselect*fromB;/*21.5.6特殊的查询合并方式*/procsql;title'AEXCLUSIVEUNIONB';(select*fromAexceptselect*fromB)union(select*fromBexceptselect*fromA);

感谢各位的阅读！关于“表链接proc sql的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！