topic Re: How do I write this proc sql statement using SAS datastep ? in SAS Programming

How do I write this proc sql statement using SAS datastep ?

bobbyc — Tue, 20 Sep 2016 11:57:20 GMT

proc sql noprint;
create table test as
select a.*, b.one
from dat1 as a left join
dat2 as b
on a.id eq b.id and ((a.date1 eq b.date1) or (b.date1 <= a.date1 <= b.date2))
quit;

How do I write this using merge statement ?

I was able to write the below, but how do I add the condition (b.date1 <= a.date1 <= b.date2)) ?

data test;
merge dat1(in=a) dat2(in=b);
by id date1;
if a;
run;

Thanks for your help

Re: How do I write this proc sql statement using SAS datastep ?

Loko — Tue, 20 Sep 2016 12:04:18 GMT

Hello,

data test;
merge dat1(in=a) dat2(in=b rename=(date1=b_date1 date2=b_date2));
by id ;
if a and b_date1 <= date1 <= b_date2;
run;

Re: How do I write this proc sql statement using SAS datastep ?

Kurt_Bremser — Tue, 20 Sep 2016 12:34:53 GMT

I guess that this is one of the cases where SQl can do things that the data step can't, since we could do the merge on id alone, but a subsequent subsetting if with the further conditions would prevent the output of an "a" record if there were only matching "b" IDs that don't fulfil the additional conditions.