SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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
Diamond | Level 26

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?

--
Paige Miller
supersasnewbie
Calcite | Level 5
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
Diamond | Level 26

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;
--
Paige Miller

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 740 views
  • 1 like
  • 3 in conversation