How to chose closer date ?

[SASdevAnneMarie]

Hello Experts,

I have 2 datasets, I would like to do a proc sql left join between data 1 et data 2 to display the most closest date for DATE 1 column from DATE 2 column. Do you know, please,  some sas function for closer date ?

I added the data.

Thank you !

[Ksharp · Accepted solution]

libname x v9 'c:\temp';

proc sql;
create table temp as
select a.*,b.date2,b.date2-a.date as diff
 from x.data1 as a left join x.data2 as b
  on a.no_k=b.no_k 
   order by a.no_k,a.date,diff
 ;
 quit;
 data temp2;
  set temp;
  by no_k date;
  if first.date then vname='Date2_closer_min';
  if last.date  then vname='Date2_closer_max';
if first.date or last.date;
run;
proc transpose data=temp2 out=want;
by no_k date;
var date2;
id vname;
run;

[SASdevAnneMarie]

Thank you Ksharp,

The code is OK, but I need the closest day.
Do you know, please, how to add in sql condition that I need hold only 2 rows : when diff is min for positive values of diff and when diff is min of negative values of diff ?
Thank you very much !

[Ksharp]

You need ABS() function.

libname x v9 'c:\temp';

proc sql;
create table want as
select a.*,b.date2,b.date2-a.date as diff
 from x.data1 as a left join x.data2 as b
  on a.no_k=b.no_k 
   where calculated diff>=0
    group by a.no_k,a.date
     having abs(calculated diff)=min(abs(calculated diff))
union 
select a.*,b.date2,b.date2-a.date as diff
 from x.data1 as a left join x.data2 as b
  on a.no_k=b.no_k 
   where calculated diff<0
    group by a.no_k,a.date
     having abs(calculated diff)=min(abs(calculated diff))
 ;
 quit;

[andreas_lds]

What do you except as result actually?

Have a look at the function intck, but you can just use subtraction (along with abs function) to get the difference of two dates.

[SASdevAnneMarie]

I don't know how to select, in proc sql, the min for positive values of diff (to have the 25/11/2022) and the max for negatives diff (to have the 11/11/2022).

Thank you for your help !

[andreas_lds]

Sorry, i don't get it. In the data you have posted date1 is always smaller than date2.