PROC SQL JOIN Question

[Ranjeeta]

Hello,

I would appreciate if you can advise on the behavior of the proc sql join 

When I omit the bolded in the select statement i get the exact no of obs as in the A dataset i.e. 81 rows 

When I include the bolded then there are more than 81 obs returned .. I believe there are some multile matches occuring 

but need help in understanding what is happening and how to include some variables from the other datasets such as N , D and at the same time have only 81 obs which is the no of rows in my A datset.

PROC SQL;
CREATE TABLE ONEDAY AS
SELECT DISTINCT
A.*,
N.INST_NO AS TRANS_INST_NACRS,
CASE WHEN MISSING(D.CIHI_KEY)=0 THEN 'DAD'
WHEN MISSING(N.CIHI_KEY)=0 THEN 'NACRS'
ELSE ''
END AS TRANS_TYPE,
MISSING(D.CIHI_KEY)=0 AS DAD_TRANSFER,
MISSING(N.CIHI_KEY)=0 AS NACRS_TRANSFER,
MISSING(D.CIHI_KEY)=0 OR MISSING(N.CIHI_KEY)=0 AS ANY_TRANSFER
FROM FinalDADAbstracts AS A
LEFT JOIN CIHI.DAD1819Q4 AS D
ON A.HCNE=D.HCNE AND A.HCNE~='9999999999'
AND (DATEPART(D.DISCH_DT)<=DATEPART(A.ADM_DT)<=DATEPART(D.DISCH_DT)+1)
LEFT JOIN CIHI.NACRS1819Q4 AS N
ON A.HCNE=N.HCNE AND A.HCNE~='9999999999'
AND A.CIHI_KEY~=N.CIHI_KEY
AND (DATEPART(N.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(N.Reg_DT)+1)
LEFT JOIN INST_EVT AS E
ON (D.INST_NO=STRIP(PUT(E.INST_AT,BEST32.)) OR N.INST_NO=STRIP(PUT(E.INST_AM,BEST32.)))
HAVING ANY_TRANSFER=1
ORDER BY A.CIHI_KEY;
QUIT;

[ballardw]

If you have multiple values of N.inst_no for matching criteria when the join occurs that is what happens.

Example:

data work.one;
   x='A';
run;

data work.two;
   input x $1. y;
datalines;
A 1
A 2
;

proc sql;
   create table work.out as
   select distinct a.*, b.y
   from work.one as a
        left join
        work.two as b
        on a.x = b.x
   ;
quit;

If you only want one value for the N.Inst_no then you need to filter the N aliased set prior to the join in some fashion.

Distinct considers ALL the variables' levels, not just from one set. So multiple values of N.inst_no are distinct.

Please post code in a code box for readability (or do you not use any indenting of your code at all?). When coding in all caps without any indents code is very hard to read.

[Ranjeeta]

Thankyou for pointing me in the direction of the issue 

I will paste it in a box for future