- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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