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;
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;
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;
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?
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.