Desktop productivity for business analysts and programmers

Joining Dates between periods

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Joining Dates between periods

I am needing to join tables on for an event date, but to also pull in any data for one day before and one day after the event date. Not sure how to write the code for the join? 


Accepted Solutions
Solution
‎01-19-2018 12:14 PM
Occasional Contributor
Posts: 15

Re: Joining Dates between periods

[ Edited ]
Posted in reply to novinosrin

I figured it out. Simple fix. Just couldn't think at the moment. 

 

The code I used was:

 

(Left join) where t2.date between t1.date-1 and t1.date+1

 

Thanks.

 

 

View solution in original post


All Replies
PROC Star
Posts: 1,353

Re: Joining Dates between periods

Your question needs to be well explained.

1. Give us the community sample data

2. Explain your requirement referring to the data in your sample

3. Also give us the output sample that you want  

 

Please be clearly descriptive as possible. Thank you!

Solution
‎01-19-2018 12:14 PM
Occasional Contributor
Posts: 15

Re: Joining Dates between periods

[ Edited ]
Posted in reply to novinosrin

I figured it out. Simple fix. Just couldn't think at the moment. 

 

The code I used was:

 

(Left join) where t2.date between t1.date-1 and t1.date+1

 

Thanks.

 

 

PROC Star
Posts: 500

Re: Joining Dates between periods

made up an example, may be something like this.

 

data have1;
input name $ event:mmddyy10.;
format event mmddyy10.;
datalines;
smith 09/23/2005
Sam 10/11/2016
Pam 05/15/2016
;
run;


data have2;
input name $ event1:mmddyy10. price;
format event1 mmddyy10.;
datalines;
smith 09/23/2005 10
smith 09/02/2005 20
smith 09/24/2005 25
smith 09/28/2005 35
Sam 10/11/2016 22
Sam 10/12/2016 17
Sam 10/11/2016 60
Sam 10/02/2016 70
Pam 05/18/2016 50
;
run;

proc sql;
select a.name, event1, price 
from have1 a
inner join
have2 b
on a.name =b.name
and (a.event=b.event1
or a.event=b.event1 -1
or a.event=b.event1 +1);
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 149 views
  • 0 likes
  • 3 in conversation