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;
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.
Thankyou for pointing me in the direction of the issue
I will paste it in a box for future
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.