DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

[ Edited ]

 

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


Accepted Solutions
Solution
‎09-20-2016 08:39 AM
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super Contributor
Posts: 305

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

[ Edited ]

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;

 

Solution
‎09-20-2016 08:39 AM
Super User
Posts: 6,928

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 211 views
  • 0 likes
  • 3 in conversation