Need help in left join for duplicate values

[Aexor]

Need help in joining two table, Just pasted sample data here.

table1

member_id member_cd   valid_from_dttm             valid_to_dttm

545           ALL        01DEC2023:10:20:45.000000   01JAN5999:00:00:00.000000

546          EXT         01DEC2023:10:20:45.000000 01JAN5999:00:00:00.000000

1528         ALL        01DEC2023:10:40:45.000000 01JAN5999:00:00:00.000000

table2

member_id member_nm   valid_from_dttm                        valid_to_dttm

545        T1_CostINC        01DEC2023:10:30:30.000000  01DEC2023:10:30:45.000000

545        T1_SE                 01DEC2023:10:20:35.000000    01JAN5999:00:00:00.000000

546        T1_UK                01DEC2023:10:10:45.000000     01DEC2023:10:30:45.000000

546      T1_Raleigh 1       01DEC2023:10:20:45.000000     01JAN5999:00:00:00.000000

1528        XYZ             01DEC2023:10:40:45.000000       01JAN5999:00:00:00.000000

I have this kind of data , as you can see i have one to many relation, I wanted to do a left join based on member_id

also I want the coloumns where valid_to_dttm is having maximun value 

here for member_id 545 the record I want to take is of maximum valid_to_dttm value fro table2

so output expected here is 

member_id  member_cd member _nm     valid_from_dttm                    valid_to_dttm

545             ALL     T1_SE                     01DEC2023:10:20:35.000000    0 1JAN5999:00:00:00.000000

546            EXT    T1_Raleigh 1     01DEC2023:10:20:45.000000     01JAN5999:00:00:00.000000

1528           ALL        XYZ             01DEC2023:10:40:45.000000       01JAN5999:00:00:00.000000

[Patrick]

If you're also using the valid_to and valid_from columns for the join then the relationship between your tables is 1 to 1. 

If you only need active records then simply sub-set your tables to only select rows where valid_to_dttm is 01JAN5999:00:00:00.000000 There should only ever be one record per business key member_id.

I wouldn't keep the valid_from and valid_to records - but if you do then you should keep the max value for valid_from_dttm as that's the earliest datetime where the values from both tables where valid.

Create sample data

Spoiler

data table1;
  infile datalines truncover dsd;
  input member_id member_cd $ valid_from_dttm:datetime20. valid_to_dttm:datetime20.;
  format valid_from_dttm valid_to_dttm datetime20.;
  datalines;
545,ALL,01DEC2023:10:20:45.000000,01JAN5999:00:00:00.000000
546,EXT,01DEC2023:10:20:45.000000,01JAN5999:00:00:00.000000
1528,ALL,01DEC2023:10:40:45.000000,01JAN5999:00:00:00.000000
;

data table2;
  infile datalines truncover dsd;
  input member_id member_nm:$40. valid_from_dttm:datetime20. valid_to_dttm:datetime20.;
  format valid_from_dttm valid_to_dttm datetime20.;
  datalines;
545,T1_CostINC,01DEC2023:10:30:30.000000,01DEC2023:10:30:45.000000
545,T1_SE,01DEC2023:10:20:35.000000,01JAN5999:00:00:00.000000
546,T1_UK,01DEC2023:10:10:45.000000,01DEC2023:10:30:45.000000
546,T1_Raleigh 1,01DEC2023:10:20:45.000000,01JAN5999:00:00:00.000000
1528,XYZ,01DEC2023:10:40:45.000000,01JAN5999:00:00:00.000000
;

Logic that only uses active records:

proc sql;
  create table want as
  select
    t1.member_id
    ,t1.member_cd
    ,t2.member_nm
    ,max(t1.valid_from_dttm,t2.valid_from_dttm) as valid_from_dtt format=datetime20.
    ,t1.valid_to_dttm
  from table1 t1 inner join table2 t2
  on t1.member_id=t2.member_id 
    and t1.valid_to_dttm='01JAN5999:00:00:00.000000'dt
    and t2.valid_to_dttm='01JAN5999:00:00:00.000000'dt
  ;
quit;

If you also want to include the latest version of inactive (expired records) if there is no active record then your code would need to look somewhere along the line of below:

proc sql;
  create table want as
  select
    t1.member_id
    ,t1.member_cd
    ,t2.member_nm
    ,max(t1.valid_from_dttm,t2.valid_from_dttm) as valid_from_dtt format=datetime20.
    ,t1.valid_to_dttm
  from table1 t1 inner join table2 t2
  on t1.member_id=t2.member_id 
    and t1.valid_to_dttm between t2.valid_from_dttm and t2.valid_to_dttm
    and t2.valid_from_dttm between t2.valid_from_dttm and t2.valid_to_dttm
  group by t1.member_id
  having max(t1.valid_to_dttm)=valid_to_dttm and max(t2.valid_from_dttm)=t2.valid_from_dttm
  ;
quit;