SQL Nested Query

Estelle — Tue, 11 Sep 2018 20:51:01 GMT

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;

Re: SQL Nested Query

PGStats — Tue, 11 Sep 2018 21:17:51 GMT

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;

Re: SQL Nested Query

Estelle — Tue, 11 Sep 2018 21:44:52 GMT

Yep that's it thanks

topic Re: SQL Nested Query in SAS Procedures

SQL Nested Query

Re: SQL Nested Query

Re: SQL Nested Query