SQL Nested Query

[Estelle]

I haven't ever been a big nested query user, but I'm trying my hand at this one for the sake of brevity in my code. I may have a parenthesis issue, but I've tried a few different ways of adding parenthesis with no luck. Using SAS EG version 7.11.

PROC SQL;
CREATE TABLE UNQ_MEMBER AS SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM (
SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM MEMBER.MM1718
UNION 
SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM MEMBER.MM1819
ORDER BY CIN, ENDDTM
) MEMBERSHIP
GROUP BY CIN
HAVING ENDDTM = MAX(ENDDTM);
RUN;

The following are my separated queries, where I do get the correct results:

/*PULL AND CONCATENATE MEMBERSHIP FOR RELEVANT DATES*/
PROC SQL;
CREATE TABLE MEMBERSHIP AS
SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM MEMBER.MM1718
UNION 
SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM MEMBER.MM1819
ORDER BY CIN, ENDDTM;
RUN;

/*GIVES TABLE W/ MOST RECENT DATES*/
PROC SQL;
CREATE TABLE UNQ_MEMBER AS SELECT 
DISTINCT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM MEMBERSHIP
GROUP BY CIN
HAVING ENDDTM = MAX(ENDDTM);
RUN;

[PGStats · Accepted solution]

After trimming a few useless bits:

PROC SQL;
CREATE TABLE UNQ_MEMBER AS 
SELECT CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
FROM (
    SELECT 
        CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
    FROM MEMBER.MM1718
    UNION 
    SELECT 
        CIN, INSCODE, REGION, MBR_COUNTY, ENDDTM
    FROM MEMBER.MM1819 )
GROUP BY CIN
HAVING ENDDTM = MAX(ENDDTM);
QUIT;

[Estelle]

Yep that's it thanks