Link to home
Create AccountLog in
Avatar of uworlds
uworldsFlag for Australia

asked on

How do I do a LEFT JOIN in SAS where only one value is joined even if there are multiple values for the joining criteria?

How do I do a LEFT JOIN where there is only ever one value joined from the secondary table, even if multiple values exist for the join criteria?  In other words, no additional rows are created for each secondary table instance?

The code I am using is attached.
PROC SQL;
CREATE VIEW NEWJOIN AS
SELECT DISTINCT
       A.VAR1,
       A.VAR2,
       A.VAR3,
       B.VAR2,
       A.VAR4,
       A.VAR5
FROM DATA1 A LEFT JOIN DATA2 B
ON A.VAR1 = B.VAR1
GROUP BY B.VAR1
ORDER BY A.VAR1;
RUN;

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this
PROC SQL;
CREATE VIEW NEWJOIN AS
SELECT DISTINCT
       A.VAR1,
       A.VAR2,
       A.VAR3,
       B.VAR2,
       A.VAR4,
       A.VAR5
FROM DATA1 A LEFT JOIN (SELECT VAR1, MAX(VAR3) AS VAR3 FROM DATA2) B
ON A.VAR1 = B.VAR1
GROUP BY B.VAR1
ORDER BY A.VAR1;
RUN;

Open in new window

Avatar of uworlds

ASKER

Thanks very much for this.  When I do this, I seem to be getting a single MAX(VAR3) value spread across all observations rather than the MAX(VAR3) within each grouping of VAR1.   Do you know why that might be happening?  Many thanks.
PROC SQL;
CREATE VIEW ATCJOIN AS
SELECT DISTINCT
       A.UIN,
       A.CARDTYPE,
       A.SEX,
       A.DOB,
       A.SNETFLAG,
       A.PTNTLEVL,
       A.ITEMCODE,
       A.NMEMEDCN,
       A.STRENGTH,
       B.ATCCODE,
       A.DTSUPPLY,
       A.DTPRCESS,
       A.TOTSUPPL,
       A.TOTRPTS,
       A.TOTBPAID
FROM DRUGJSRT A LEFT JOIN
(SELECT ITEMCODE, MAX(ATCCODE) AS ATCCODE FROM ITEMMEAN) B
ON A.ITEMCODE = B.ITEMCODE
GROUP BY B.ITEMCODE
ORDER BY A.ITEMCODE;
RUN;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of uworlds

ASKER

Hello ee_rlee,  Thank you so much for your solution, I really appreciate it!  Because your first solution which was so good I was able to deduce the nested GROUP clause and then you confirmed it as well and it worked.  You have really made my day so thank you!  Mike
you're welcome ;)