ERROR: Unresolved reference to table/correlation name (SAS, PROC SQL)

Question

I got the error message "ERROR: Unresolved reference to table/correlation name for every column name when I wasn't using the full table names. Now I'm using full table names and getting the same error. Not sure what else to try - I'm very new to SQL and still don't understand much of how things work.

Here is a copy of the log:

171        PROC SQL;
172        CREATE table Spillover AS select
172      ! Y16_CALC_STUD.Student_ID,Y16_LINK_GM.score,Y16_CALC_STUD.StudSGP,Y16_CALC_STUD.g    rade_subject_ID,Y16_LINK_GM.employeeID,Y1
172      !     6_GROW_TEACHER.HEDI,Y16_GROW_TEACHER.HEDI_points,Y16_GROW_TEACHER.avg_prescore
173        from (select Y16_CALC_STUD.Student_ID, Y16_CALC_STUD.StudSGP, Y16_CALC_STUD.CalcID, Y16_LINK_GM.school_DBN,
173      ! Y16_LINK_GM.Score, Y16_LINK_GM.employeeID, Y16_LINK_GM.grade_subject_ID
174        from  Y16_CALC_Stud as a left join Y16_LINK_GM as link
175        on link.Student_ID = link.Student_ID)
176        as c left join Y16_GROW_Teacher as d on
177        Y16_CALC_Stud.StudCalcID = Y16_GROW_Teacher.calcID and Y16_LINK_GM.employeeID=Y16_GROW_Teacher.employeeID and
177      ! Y16_CALC_Stud.grade_subject_ID=Y16_GROW_Teacher.GRADE_SUBJECT;
ERROR: Unresolved reference to table/correlation name Y16_CALC_Stud.
ERROR: Unresolved reference to table/correlation name Y16_LINK_GM.
ERROR: Unresolved reference to table/correlation name Y16_CALC_Stud.
ERROR: Unresolved reference to table/correlation name Y16_CALC_STUD.
ERROR: Unresolved reference to table/correlation name Y16_LINK_GM.
ERROR: Unresolved reference to table/correlation name Y16_CALC_STUD.
ERROR: Unresolved reference to table/correlation name Y16_CALC_STUD.
ERROR: Unresolved reference to table/correlation name Y16_LINK_GM.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I get the same errors in Base SAS and Enterprise Guide.

Answer 1

In SAS's proc sql, you refer to the datasets you want to join in your from and join statements, not in your select statement. SAS interprets Y16_CALC_STUD.Student_ID as you referring to the library called Y16_CALC_STUD and using the dataset called Student_ID in that library, which is not what I think you're trying to do here. So I just replaced all of those with a, b, and c and referred to your three datasets as a, b, and c down in the from/join section of your code.

PROC SQL;
    CREATE table Spillover AS select 
        a.Student_ID, b.score, a.StudSGP, a.grade_subject_ID, b.employeeID,
        c.HEDI, c.HEDI_points, c.avg_prescore

        from Y16_CALC_STUD as a 
        left join Y16_LINK_GM as b
        on a.Student_ID = b.Student_ID
        left join Y16_GROW_Teacher as c
        on a.StudCalcID = c.calcID and a.employeeID = c.employeeID and a.grade_subject_ID = c.GRADE_SUBJECT;
quit;

Answer 2

Your subquery is missing closing the right parentheses and you are defining the results to use aliases like a,b,c. But in your list of columns you are not using the aliases you defined.

Probably easiest to just fix the alias where you are defining them rather than update every reference in the column list.

(select Y16_CALC_STUD.Student_ID
      , Y16_CALC_STUD.StudSGP
      , Y16_CALC_STUD.CalcID
 from Y16_CALC_Stud
) as Y16_CALC_Stud

Answer 3

Fixed by removing all aliases and using the following:

PROC SQL;
CREATE table Spillover AS select Y16_CALC_STUD.Student_ID,Y16_LINK_GM.score,Y16_CALC_STUD.StudSGP,Y16_CALC_STUD.g    rade_subject_ID,Y16_LINK_GM.employeeID,Y16_GROW_TEACHER.HEDI,Y16_GROW_TEACHER.HEDI_points,Y16_GROW_TEACHER.avg_prescore
from (select Y16_CALC_STUD.Student_ID, Y16_CALC_STUD.StudSGP, Y16_CALC_STUD.CalcID, Y16_LINK_GM.school_DBN, Y16_LINK_GM.Score, Y16_LINK_GM.employeeID, Y16_LINK_GM.grade_subject_ID
from  Y16_CALC_Stud left join Y16_LINK_GM
on Y16_LINK_GM.Student_ID = Y16_LINK_GM.Student_ID) 
left join Y16_GROW_Teacher on
Y16_CALC_Stud.CalcID = Y16_GROW_Teacher.calcID and Y16_LINK_GM.employeeID=Y16_GROW_Teacher.employeeID and Y16_LINK_GM.grade_subject_ID=Y16_GROW_Teacher.GRADE_SUBJECT;

Answer 4

First of all - your code has made a subquery and you are referencing the sub query with an alias - "C". I have updated the query so that your subquery C has included "Y16_CALC_Stud.StudCalcID" which is being referred in the main query.

Apart from this, Your main query you are referring to the variable with the main dataset names,i.e. Y16_CALC_STUD.CalcId which has to be referred as c.CalcID since you have provided an alias.

Lastly your errors which say "Expression using equals (=) has components that are of different data types." are because the join conditions you have used are different data type. For example "c.grade_subject_ID = Y16_GROW_Teacher.GRADE_SUBJECT" both c.grade_subject_id and Grade_subject might have different data types. One might be numeric and the other might be character. You will have to type cast before you join.

    PROC SQL;
    CREATE table Spillover AS 
select c.Student_ID,
    c.score,
    c.StudSGP,
    c.grade_subject_ID,
    c.employeeID,
    d.HEDI,
    d.HEDI_points,
    d.avg_prescore

    from 
    (select 
        Y16_CALC_STUD.Student_ID, 
        Y16_CALC_STUD.StudSGP, 
        Y16_CALC_STUD.CalcID, 
        Y16_LINK_GM.school_DBN,
        Y16_LINK_GM.Score, 
        Y16_LINK_GM.employeeID, 
        Y16_LINK_GM.grade_subject_ID,
        Y16_CALC_Stud.StudCalcID
    from  Y16_CALC_Stud as a 
left join Y16_LINK_GM as link on a.Student_ID = link.Student_ID) as C
    left join Y16_GROW_Teacher as d on c.StudCalcID = Y16_GROW_Teacher.calcID 
and c.employeeID = Y16_GROW_Teacher.employeeID 
and c.grade_subject_ID = Y16_GROW_Teacher.GRADE_SUBJECT;

This above query should solve all your errors except the datatype issue.