How to Select Data with Newest Load Date?

[supersasnewbie]

Hello!

I am looking to create some code that pulls only the newest SBSCR_NBR, SUB_FST_NM, and SUB_LST_NM based on the newest LOAD_DT (Date9. format). SBSCR_NBR should be unique, but sometimes people have multiple names in the system (i.e.: Jim/James, Bob/Robert for first name, and last names can change if maiden names change). Having multiple instances of SUB_FST_NM, and SUB_LST_NM included in this table causes duplicates in the data that I am joining to. Can someone help me figure this one out?

My current code is as follows:

CREATE TABLE subtable AS

SELECT DISTINCT SBSCR_NBR

,SUB_FST_NM

,SUB_LST_NM

,LOAD_DT

FROM MEMBERTBL

WHERE RCD in ("EM");

QUIT;

[PeterClemmensen]

Untested since I can't see your data. But I think you want something like this

proc sql;
   create table subtable as
   select SBSCR_NBR
        , SUB_FST_NM
        , SUB_LST_NM
        , LOAD_DT
   from MEMBERTBL
   having max(LOAD_DT) = LOAD_DT;
   ;
quit;

[PaigeMiller]

I don't see where you have described the desired output. Newest load date ... according to what separation? Do you want newest load date by SBSCR_NBR? You don't say that. Or do you want newest load date by some combination of SUB_FST_NM and SUB_LST_NM? Or do you want something else?

[supersasnewbie]

Hello,

I'm looking for the newest load date by SBSCR_NBR. So for example, there could be data such as:

LOAD_DT / SBSCR_NBR / SUB_FST_NM / SUB_LST_NM
05AUG2022 / 000001 / JAMES / DOE
01JUL2022 / 000001 / JIM / DOE
12DEC2011 / 000001 / JAMES / DOE
02AUG2022 / 000002 / RICK / THOMAS
01JUL2022 / 000002 / RICHARD / THOMAS

Since there are two SBSCR_NBRs in this table, 000001 and 000002, with multiple variations of names, I'm looking to create a table only with the latest dated version by SBSCR_NBRs. Ultimately, the end result should be a table with:

LOAD_DT / SBSCR_NBR / SUB_FST_NM / SUB_LST_NM
05AUG2022 / 000001 / JAMES / DOE
02AUG2022 / 000002 / RICK / THOMAS

[PaigeMiller · Accepted solution]

Thanks. I think a slight modification to the code from @PeterClemmensen is needed.

proc sql;
   create table subtable as
   select SBSCR_NBR
        , SUB_FST_NM
        , SUB_LST_NM
        , LOAD_DT
   from MEMBERTBL
   group by sbscr_nbr
   having max(LOAD_DT) = LOAD_DT;
   ;
quit;