Need help in left join for duplicate values

Aexor — Fri, 09 Feb 2024 04:41:05 GMT

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

Re: Need help in left join for duplicate values

Patrick — Fri, 09 Feb 2024 05:47:06 GMT

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

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;

topic Re: Need help in left join for duplicate values in SAS Programming

Need help in left join for duplicate values

Re: Need help in left join for duplicate values