topic PROC SQL JOIN Question in SAS Programming

PROC SQL JOIN Question

Ranjeeta — Thu, 17 Oct 2019 15:52:02 GMT

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;

Re: PROC SQL JOIN Question

ballardw — Thu, 17 Oct 2019 16:17:38 GMT

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.

Re: PROC SQL JOIN Question

Ranjeeta — Thu, 17 Oct 2019 17:31:20 GMT

Thankyou for pointing me in the direction of the issue

I will paste it in a box for future