Apologies, as i have tried to sample down the data i have made some minor errors in the dates. To your point on what i am after, i am only wanting the data to merge when the conditions within the statements are met. e.g. if date1 = 01jun2020 and date2 = 03jun2020 then i wouldnt want this to merge. I have included the amended code below; %let month00 = 01jun2020;
%let month01 = 01jul2020;
%let month02 = 01aug2020;
%let date00 = 04jun2020;
%let date01 = 08jul2020;
%let date02 = 06aug2020;
%let date_end00 = 07jul2020;
%let date_end01 = 05aug2020;
%let date_end02 = 08sep2020;
proc sql;
create table test_merge as select a.*,b.*
from trial as a left join trial2 as b
on a.id=b.id and
((a.date1 = "01jun2020"d, and b.date2 between "04jun2020"d, and "07jul2020"d) or
(a.date1 = "01jul2020"d, and b.date2 between " 08jul2020"d, and "05aug2020"d) or
(a.date1 = "01aug2020"d, and b.date2 between "06aug2020"d, and "08sep2020"d));
quit; The macro dates equivilent would be; proc sql;
create table test_merge as select
a.*,
b.*
from trial as a
left join trial2 as b
on a.id=b.id
and
((a.date1 = "month00."d, and b.date2 between "date00."d, and "date_end00."d)
or
(a.date1 = "month01."d, and b.date2 between "date01."d, and "date_end01."d)
or
(a.date1 = "month02."d, and b.date2 between "date02."d, and "date_end02."d)
quit;
... View more