topic Re: Left join a date to row with max date where duplicate records exist in SAS Programming

Left join a date to row with max date where duplicate records exist

TheNovice — Tue, 15 Sep 2020 02:29:51 GMT

Hi all,

I have to solve this using proc sql because it will then be converted to a sql script for automation and needs to be as close as possible (just how things work here).

My dataset has duplicate records like below with different dates. I need to join to a second table where b.date > a.date. But i only want to join to the closest match to b.date which is the max (a.date)

Table a
BAN	col_date
123	20-Jun-20
123	21-Jul-20

Table b
status	status_date
r	20-Aug-20

to get

Table a
BAN	col_date	status	status_date
123	20-Jun-20
123	21-Jul-20	r	20-Aug-20

Would appreciate any and all guidance. thank you

Re: Left join a date to row with max date where duplicate records exist

PGStats — Tue, 15 Sep 2020 04:08:45 GMT

There is no BAN in table b?

Re: Left join a date to row with max date where duplicate records exist

TheNovice — Tue, 15 Sep 2020 04:10:38 GMT

Apologies, yes, the join key would be ban

Re: Left join a date to row with max date where duplicate records exist

andreas_lds — Tue, 15 Sep 2020 04:49:25 GMT

@TheNovice wrote:
Apologies, yes, the join key would be ban

Then, again: why is the variable BAN not in the second table?

Re: Left join a date to row with max date where duplicate records exist

Shmuel — Tue, 15 Sep 2020 06:41:17 GMT

@TheNovice wrote:
Apologies, yes, the join key would be ban

According to above I have added BAN to table b.

Next code is tested:

data table_a;
 input ban col_date :date9.;
 format col_date date9.;
cards;
123 20-jun-20
123 21-jul-20
; run;

data table_b;
 input ban status $ status_date :date9.;
 format status_date date9.;
cards;
123 r 20-aug-20
; run;

proc sql;
 create table want(drop=dif_date min_date) as
 select a.ban, a.col_date,
        b.status_date - a.col_date as dif_date,
        min(calculated dif_date) as min_date,
        case when calculated dif_date = calculated min_date
             then b.status 
             else  ' ' end as status ,
        case when calculated dif_date = calculated min_date
             then b.status_date 
             else  .   end as status_date format=date9.
 from table_a as a 
 left join table_b as b 
 on a.ban = b.ban 
 group by a.ban 
;
quit;

Re: Left join a date to row with max date where duplicate records exist

Ksharp — Tue, 15 Sep 2020 11:49:08 GMT

data table_a;
 input ban col_date :date9.;
 format col_date date9.;
cards;
123 20-jun-20
123 21-jul-20
; run;

data table_b;
 input ban status $ status_date :date9.;
 format status_date date9.;
cards;
123 r 20-aug-20
; run;

proc sql;
 create table want as
 select a.ban, a.col_date,

 case when abs(a.col_date-b.status_date)=
 min(abs(a.col_date-b.status_date)) then b.status
 else ' ' end as status
 ,
 case when abs(a.col_date-b.status_date)=
 min(abs(a.col_date-b.status_date)) then b.status_date
 else . end as status_date format=date9.

 from table_a as a 
 left join table_b as b 
 on a.ban = b.ban ;
 quit;

Re: Left join a date to row with max date where duplicate records exist

PGStats — Tue, 15 Sep 2020 18:49:46 GMT

I think @Ksharp 's answer doesn't quite answer the question. This might do better:

data a;
 input ban col_date :date9.;
 format col_date date9.;
cards;
123 20-jun-20
123 21-jul-20
567 20-jun-20
567 21-jul-20
;

data b;
 input ban status $ status_date :date9.;
 format status_date date9.;
cards;
123 r 20-aug-20
456 r 20-aug-20
;

proc sql;
 
 select a.ban, col_date,

 case when col_date = max(col_date) then status
 else ' ' end as status
 ,
 case when col_date = max(col_date) then status_date
 else . end as status_date format=date9.

 from a 
 left join b 
 on a.ban = b.ban and status_date > col_date
 group by a.ban;
 quit;

Re: Left join a date to row with max date where duplicate records exist

TheNovice — Tue, 15 Sep 2020 23:44:42 GMT

Thank you all so much. all the solutions work 🙂 I will tag the first one with the accept then. thanks again

Re: Left join a date to row with max date where duplicate records exist

Ksharp — Wed, 16 Sep 2020 11:13:13 GMT

PG,
Yes. I misunderstood something in original post .