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
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.
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;
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"]).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.