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 !
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;
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;
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 !
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;
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.
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 !
Sorry, i don't get it. In the data you have posted date1 is always smaller than date2.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.