topic Re: How to chose closer date ? in SAS Programming

How to chose closer date ?

SASdevAnneMarie — Thu, 01 Dec 2022 22:37:42 GMT

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 !

Re: How to chose closer date ?

Ksharp — Fri, 02 Dec 2022 03:27:01 GMT

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;

Re: How to chose closer date ?

SASdevAnneMarie — Fri, 02 Dec 2022 10:44:31 GMT

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 !

Re: How to chose closer date ?

andreas_lds — Fri, 02 Dec 2022 11:50:15 GMT

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.

Re: How to chose closer date ?

SASdevAnneMarie — Fri, 02 Dec 2022 12:23:53 GMT

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 !

Re: How to chose closer date ?

andreas_lds — Fri, 02 Dec 2022 12:53:26 GMT

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

Re: How to chose closer date ?

Ksharp — Sat, 03 Dec 2022 03:00:13 GMT

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;