I have a piece of code that does what I need. But it looks complicated and messy for what it does. Is there a simpler way to look up the member CIN unqiuely without having a create a sub-query? Get the unique items from DATA (often it has hundreds of duplicate ID), but each ID has their own service date. Sometimes a ID has 20-30 service date in the input.Lookup their member ID from a different table Sometimes there are 2-3 member ID for each Personal IDJoin it back together with originalI am using SAS Enterprise Guide 6.1, thanks!/* Look up the member ID using Personal ID*/PROC SQL;CREATE TABLE WORK.MemberID AS SELECT c.PersonalID,     c.SBSID,    c.SITEID,    d.DateOfService FORMAT=MMDDYY10.,    INTNX('month', d.DateOfService, 0, 'END') AS EndOfMonth FORMAT=MMDDYY10.,    d.RateCode,     d.TCN,    d.ClaimAmount FORMAT=DOLLAR10.2/* Get every Membmer ID associated with the Personal Number*/FROM SASUSER.DATA d    RIGHT JOIN (    /*****************************************************/    /* Subquery 2: to Match the PersonalID with MemberID */        SELECT PersonalID,             MEMBERID AS SBSID,            SITEID        FROM DW_SOME_TABLE b INNER JOIN (         /****************************************************/        /* Subquery 1: Select only the distinct Personal ID */            SELECT DISTINCT PersonalID             FROM SASUSER.DATA             WHERE PersonalID is not null        ) a         /* End of Subquery 1*/         /****************************************************/        ON (a.PersonalID = b.CINNUMBER)        WHERE (b.SITEID = '0001')    ) c ON (c.PersonalID = d.PersonalID)    /* End of Subquery 2*/    /*****************************************************/ORDER BY c.PersonalID;QUIT;
SQL: How to only look up unique values
sql;sas