topic Re: How to fix ERROR: Unresolved reference to table/correlation name A in SAS Programming

How to fix ERROR: Unresolved reference to table/correlation name A

guest1994 — Mon, 07 Oct 2019 11:49:16 GMT

Hi,

I'm trying to inner join table A on to B, including other subsequent joins as seen below in my code.

I had selected the columns I needed and have included other columns I am extracting also, from the tables I'm joining.

PROC SQL; 
	CREATE TABLE SPDSWORK.CORE_APP AS 
	SELECT 
	A.CSID
	,A.CSREQUESTNUMBER
	,A.TRANSACTION_DTTM
	,A.PROCESSED_DTTM
	,A.PRODUCT_CATEGORY
	,A.APPLICATIONSCORING
	,A.SOURCEOFAPPLICATION 
	,A.GUARANTEEDAPPLICATIONIND 
	,A.REPROCESSQUANTITY 
        ,B.APPLICANT_SEQ
	,B.CIDPERSID AS CUS1_CIDPERSID 
        ,C.CIDPERSID AS CUS2_CIDPERSID 
	,D.SCOREMODELNAME 
	,E.OPENED_DT
	,1 AS NB_FLAG
	FROM SPDSWORK.OMDM_APPLICATION A 
	INNER JOIN SPDSWORK.APPLICANT (WHERE=(APPLICANT_SEQ=1)) B
	ON A.CSID = B.CSID
	LEFT JOIN SPDSWORK.APPLICANT (WHERE=(APPLICANT_SEQ=2)) C
	ON A.CSID = C.CSID
	INNER JOIN SPDSWORK.APPLICATIONSCORING D
    ON A.CSID = D.CSID
	LEFT JOIN SPDSWORK.CARDS_ACC2APP_LINKAGE E
    ON A.CSID = E.CSID
;
QUIT;

I'm receiving an error message:

ERROR: Unresolved reference to table/correlation name A.

ERROR: Expression using equals (=) has components that are of different data types.

I'm not sure why I am receiving this message, especially the first one. Does anyone know how I can fix this?

Many thanks

Re: How to fix ERROR: Unresolved reference to table/correlation name A

PaigeMiller — Mon, 07 Oct 2019 12:02:27 GMT

You are trying to use an equal sign to compare two different variables that are different types ... in other words, one is character and one is numeric, and these cannot be compared directly.

Re: How to fix ERROR: Unresolved reference to table/correlation name A

utrocketeng — Mon, 07 Oct 2019 12:02:33 GMT

i am not familiar with using the "(WHERE({COLNAME}={VALUE})). if it works, then cool... otherwise, you may want to try the code below. i think it ought to work.

PROC SQL; 
	CREATE TABLE SPDSWORK.CORE_APP AS 
	SELECT 
	A.CSID
	,A.CSREQUESTNUMBER
	,A.TRANSACTION_DTTM
	,A.PROCESSED_DTTM
	,A.PRODUCT_CATEGORY
	,A.APPLICATIONSCORING
	,A.SOURCEOFAPPLICATION 
	,A.GUARANTEEDAPPLICATIONIND 
	,A.REPROCESSQUANTITY 
        ,B.APPLICANT_SEQ
	,B.CIDPERSID AS CUS1_CIDPERSID 
        ,C.CIDPERSID AS CUS2_CIDPERSID 
	,D.SCOREMODELNAME 
	,E.OPENED_DT
	,1 AS NB_FLAG
	FROM SPDSWORK.OMDM_APPLICATION A 
	INNER JOIN SPDSWORK.APPLICANT  B
	ON A.CSID = B.CSID AND  b.APPLICATNT_SEQ=1
	LEFT JOIN SPDSWORK.APPLICANT  C
	ON A.CSID = C.CSID AND  c.APPLICATNT_SEQ=2
	INNER JOIN SPDSWORK.APPLICATIONSCORING D
    ON A.CSID = D.CSID
	LEFT JOIN SPDSWORK.CARDS_ACC2APP_LINKAGE E
    ON A.CSID = E.CSID
;
QUIT;

Re: How to fix ERROR: Unresolved reference to table/correlation name A

FreelanceReinh — Mon, 07 Oct 2019 12:36:29 GMT

Hi @guest1994,

Your code is syntactically correct and works for suitable data. You would get the first error message if you omitted the "A" after "SPDSWORK.OMDM_APPLICATION". Most likely, the second error message is just a consequence of not recognizing A.CSID as the numeric value CSID from that dataset (and hence this error should vanish once the first issue is fixed).

If you need more help, please post the complete log of this PROC SQL step, i.e., the code submitted and all pertinent errors, warnings and notes. Important: Paste the log into a code window (opened with the {i} button [tool tip: "Insert Code"]).