时间戳

首页 > 解决方案 > 在 PROC SQL 中将表连接在一起

sas - 在 PROC SQL 中将表连接在一起

问题描述

我想将我创建的两个表连接到一个表中，但我收到一个语法错误，提示在同一范围内的多个表中找到 ColumnOverallStudentReport.ID。如果有人可以帮助解决这个语法错误，那将不胜感激，或者如果有人有更好的方法将这两个表连接在一起，这也会有所帮助。下面的代码创建了我的第一个表

 PROC SQL;
Create table SemesterReport1 as select coalesce(A.ID,B.ID,C.ID,D.ID,E.ID) as ID, 
    coalesce(A.Year,B.Year,C.Year,D.Year,E.Year) as Year, coalesce(A.Term,B.Term,C.Term,D.Term,E.Term) as Term, 
    SemesterGPA.SemGPA, AccumulativeGPA.GPAAccum, 
    CreditHoursEarnedSemester.CreditHoursEarnedSemester, 
    GradedCreditHoursEarnedSemester.GradedCreditHoursEarnedSemester, 
    ClassStanding.ClassStanding 
    from SemesterGPA as A 
    full join AccumulativeGPA as B on A.ID=B.ID and A.Year=B.Year and A.Term=B.Term
    full join CreditHoursEarnedSemester as C on A.ID=C.ID and A.Year=C.Year and A.Term=C.Term
    full join GradedCreditHoursEarnedSemester as D on A.ID=D.ID and A.Year=D.Year and A.Term=D.Term
    full join ClassStanding as E on A.ID=E.ID and A.Year=E.Year and A.Term=E.Term
    order by ID, Year, Term
;
quit;

下面的代码创建了我的第二个表

PROC SQL;
Create table OverallStudentReport as select coalesce(A.ID,B.ID,C.ID,D.ID,E.ID) as ID, 
OverallGPA.TotalGPA,
OverallCreditHoursEarned.OverallCreditHoursEarned,
OverallGradedCreditHoursEarned.OverallGradedCreditHoursEarned,
RepeatClasses.RepeatClasses,
GradeCounts.ACount,GradeCounts.BCount,GradeCounts.CCount,GradeCounts.DCount,
GradeCounts.ECount, GradeCounts.WCount
from OverallGPA as A
full join OverallCreditHoursEarned as B on A.ID=B.ID
full join OverallGradedCreditHoursEarned as C on A.ID=C.ID
full join RepeatClasses as D on A.ID=D.ID
full join GradeCounts as E on A.ID=E.ID
order by ID
;
quit;

下面的代码应该加入上面创建的两个表，但存在语法错误。

PROC SQL;
Create table Report1 as select *
from SemesterReport1, OverallStudentReport
full join
OverallStudentReport
on SemesterReport1.ID=OverallStudentReport.ID
order by ID
;
quit;

这是我的日志

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK；

72
73 处理 SQL；

74 创建表 Report1 作为选择 *

75 来自 SemesterReport1，OverallStudentReport

76 完全加入

77 总体学生报告

78 在 SemesterReport1.ID=OverallStudentReport.ID

79按ID订购

80;

错误：在同一范围内的多个表中找到列OverallStudentReport.ID。

警告：名为 ID 的列在选择表达式（或视图）中重复。对它的明确引用将是第一个。

注意：PROC SQL 设置选项 NOEXEC 并将继续检查语句的语法。

81 退出；

注意：SAS 系统因错误而停止处理此步骤。

注意：使用的 PROCEDURE SQL（总处理时间）：

标签： sasproc-sql

解决方案

当您分配表别名时，您应该在整个查询中始终如一地使用它们，而不仅仅是在SELECT和中选择性地使用它们JOIN。此外，字段 inORDER BY是模棱两可的。由于您需要计算列 in SELECTusecalculated关键字。

顺便说一句，请参阅要踢的坏习惯：使用表别名，如 (a, b, c) 或 (t1, t2, t3)。相反，请使用与原始表名对齐的更多信息速记别名。考虑以下调整：

PROC SQL;
    create table SemesterReport1 as 
    select coalesce(s.ID, a.ID, ch.ID, g.ID, cs.ID) as Final_ID 
         , coalesce(s.Year, a.Year, ch.Year, g.Year, cs.Year) as Final_Year 
         , coalesce(s.Term, a.Term, ch.Term, g.Term, cs.Term) as Final_Term
         , s.SemGPA
         , a.GPAAccum
         , ch.CreditHoursEarnedSemester
         , g.GradedCreditHoursEarnedSemester
         , cs.ClassStanding 
    from SemesterGPA as s
    full join AccumulativeGPA as a
       on s.ID = a.ID 
       and s.Year = a.Year 
       and s.Term = a.Term
    full join CreditHoursEarnedSemester as ch
       on s.ID = ch.ID 
       and s.Year = ch.Year 
       and s.Term = ch.Term
    full join GradedCreditHoursEarnedSemester as g 
       on s.ID = g.ID 
       and s.Year = g.Year 
       and s.Term = g.Term
    full join ClassStanding as cs 
       on s.ID = cs.ID  
       and s.Year = cs.Year 
       and s.Term = cs.Term
    order by calculated Final_ID
           , calculated Final_Year
           , calculated Final_Term;
quit;

PROC SQL;
    create table OverallStudentReport as 
    select coalesce(og.ID, och.ID, ogch.ID, r.ID, gc.ID) as Final_ID
         , og.TotalGPA
         , och.OverallCreditHoursEarned
         , ogch.OverallGradedCreditHoursEarned
         , r.RepeatClasses
         , gc.ACount
         , gc.BCount
         , gc.CCount
         , gc.DCount
         , gc.ECount
         , gc.WCount
    from OverallGPA as og
    full join OverallCreditHoursEarned as och 
       on og.ID = och.ID
    full join OverallGradedCreditHoursEarned as ogch 
       on og.ID = ogch.ID
    full join RepeatClasses as r 
       on og.ID = r.ID
    full join GradeCounts as gc 
       on og.ID = gc.ID
    order by calculated Final_ID;
quit;

然后在最终查询中，不要重复 table OverallStudentReport。你应该限定ID（这里是Final_ID） in order by。并看到另一个要改掉的习惯：为什么 SELECT * 被认为是有害的？

PROC SQL; 
    create table Report1 as 
    select smr.Final_ID as ID
         , smr.Final_Year as Year
         , smr.Final_Term as Term
         , smr.SemGPA 
         , smr.GPAAccum 
         , smr.CreditHoursEarnedSemester
         , smr.GradedCreditHoursEarnedSemester
         , smr.ClassStanding 
         , osr.Final_ID 
         , osr.TotalGPA
         , osr.OverallCreditHoursEarned
         , osr.OverallGradedCreditHoursEarned 
         , osr.RepeatClasses 
         , osr.ACount 
         , osr.BCount
         , osr.CCount 
         , osr.DCount 
         , osr.ECount 
         , osr.WCount
    from SemesterReport1 smr
    full join OverallStudentReport osr
       on smr.Final_ID = osr.Final_ID 
    order by smr.Final_ID ; 
quit;

推荐阅读