topic Re: INNER JOINS of more than two tables in New SAS User

INNER JOINS of more than two tables

jgarces — Mon, 20 Dec 2021 19:25:35 GMT

Attempting to JOIN 10 tables, but having trouble Joining 3 and want to know what I am doing incorrectly? the code in RED is what I added (in steps) to add another table, but I get NO data back in the data set. If I leave this out, I get data back.

Using: SAS EG Version 7.1

Code is below:

PROC SQL outobs=40;
CREATE TABLE Funding_Data_Table_001 AS
SELECT
FA.FINANCIAL_ACCOUNT_NUMBER
/*,CT.ROW_WID
,CT.FIRST_NAME
,CT.LAST_NAME
,CT.TIME_ZONE */
,FA.STATUS
,FA.ROW_WID
,FA.ACCOUNT_NUMBER
,LO.CR_SCOR
,LO.PRCSS_MO
,LO.PRCSS_YR
,LO.DLNQNT_30_59_CN
,LO.DLNQNT_10_29_CN
,LO.DLNQNT_60_89_CN
,LO.DLNQNT_90_CN
,LO.OS_AMT_D
,LO.CUST
,LO.MK
,LO.MDL
,LO.MDL_YR
,LO.MDL_D
,LO.DPD_NB as dys_pymnt_pst_due
,LO.BOOK_DT
,LO.MDL_YR_D
,LO.VIN
,LO.REPO_DT
,LO.CHRGOF_AM
,LO.LN_BAL
/* ABOVE AND BELOW, IS THE STATE CODES FROM BOTH TABLES AS A CROSS CHECK */
,FA.CUSTOMER_STATE_CODE
,LO.CSTMR_ST_CD
,FA.ACCOUNT_NUMBER
FROM OBII.FINANCIAL_ACCOUNT FA
INNER JOIN OBII.DW_LOAN LO
ON FA.ACCOUNT_NUMBER=LO.CUST
AND LO.BK = FA.BANK_CODE
AND LO.BR = FA.BRANCH_CODE
INNER JOIN OBII.CONTACT CT
ON FA.ROW_WID = CT.ROW_WID
WHERE PRCSS_YR = 2021
AND PRCSS_MO =11
AND BOOK_DT BETWEEN '01JAN2021'D AND '01JUN2021'D;

QUIT;

Re: INNER JOINS of more than two tables

SASKiwi — Mon, 20 Dec 2021 19:43:00 GMT

That means OBII.CONTACT has no rows matching on ROW_WID for the result set you have built from the other tables. If you want the result set to have rows regardless of whether the joining table is contributing rows or not then use a LEFT JOIN rather than an INNER JOIN.

Re: INNER JOINS of more than two tables

jgarces — Mon, 20 Dec 2021 19:44:46 GMT

I will give that a shot and thank you for your input.

Sincerely, JGarces

Re: INNER JOINS of more than two tables

jgarces — Mon, 20 Dec 2021 19:48:15 GMT

REPLACE ALL the INNER JOINS with LEFT JOINS, or the one that is highlighted in RED?

Re: INNER JOINS of more than two tables

SASKiwi — Mon, 20 Dec 2021 21:09:41 GMT

Try it and see if it what you want. It will mean your row count will match the number of rows selected from your first table.