Joining Together Tables in PROC SQL

Question

I want to join two tables that I created into one table but I am getting a syntax error that says Column OverallStudentReport.ID was found in more than one table in the same scope. If anyone could help fix this syntax error that would be appreciated or if anyone has a better way to join these two tables together into one that would be helpful as well. The code below created my first table

 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;

The code below created my second table

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;

and the code below is supposed to join the two tables created above but there is a syntax error.

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

Here is my log

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72
73 PROC SQL;

74 Create table Report1 as select *

75 from SemesterReport1, OverallStudentReport

76 full join

77 OverallStudentReport

78 on SemesterReport1.ID=OverallStudentReport.ID

79 order by ID

80 ;

ERROR: Column OverallStudentReport.ID was found in more than one table in the same scope.

WARNING: Column named ID is duplicated in a select expression (or a view). Explicit references to it will be to the first one.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

81 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

Answer 1

When you assign table aliases, you should use them consistently throughout the query, not just selectively in SELECT and JOIN. Also, fields in ORDER BY is ambiguous. Since you require the calculated columns in SELECT use calculated keyword.

By the way, see Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3). Instead, use more informative shorthand aliases that align to original table names. Consider following adjustments:

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;

Then in final query, do not repeat table OverallStudentReport. And you should qualify the ID (here being Final_ID) in order by. And see another habit to kick: Why is SELECT * considered harmful?

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;