Desktop productivity for business analysts and programmers

merge data and check it there is data with in 7 days

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

merge data and check it there is data with in 7 days

i have two data sets data1 has one date for one ID and data2 has all the encounters dates for the ID's. What I want is to check if IDs in data1 had any encounters dates within 7 days in data2 and if they did create a new colunm that states 1 for yes and 0 for no. 

 

data Data1;
  input id date :date9.;
  format date date9.;
  datalines;1 07JAN2018
2 28MAR2018
3 14FEB2018;
data Data2; 
input id date :date9.;
format date date9.;
datalines;
1 07JAN2018
1 17JAN2018
1 14FEB2018
2 28MAR2018
2 04APR2018
2 17APR2018
3 14FEB2018
3 18FEB2018
3 27MAR2018
;

output : 

ID  Date     SEEN
1 07JAN2018 0 2 28MAR2018 1 3 14FEB2018 1

 


Accepted Solutions
Solution
‎04-25-2018 05:16 PM
Super User
Posts: 2,050

Re: merge data and check it there is data with in 7 days

[ Edited ]
data Data1;
  input id date :date9.;
  format date date9.;
  datalines;
1 07JAN2018
2 28MAR2018
3 14FEB2018
;

data Data2; 
input id date :date9.; format date date9.; 
datalines; 1 07JAN2018 1 17JAN2018 1 14FEB2018 2 28MAR2018 2 04APR2018 2 17APR2018 3 14FEB2018 3 18FEB2018 3 27MAR2018 ; proc sql; create table want as select a.*,max(0<b.date-a.date<=7) as seen from Data1 a left join Data2 b on a.id=b.id group by a.id,a.date; quit;

View solution in original post


All Replies
Solution
‎04-25-2018 05:16 PM
Super User
Posts: 2,050

Re: merge data and check it there is data with in 7 days

[ Edited ]
data Data1;
  input id date :date9.;
  format date date9.;
  datalines;
1 07JAN2018
2 28MAR2018
3 14FEB2018
;

data Data2; 
input id date :date9.; format date date9.; 
datalines; 1 07JAN2018 1 17JAN2018 1 14FEB2018 2 28MAR2018 2 04APR2018 2 17APR2018 3 14FEB2018 3 18FEB2018 3 27MAR2018 ; proc sql; create table want as select a.*,max(0<b.date-a.date<=7) as seen from Data1 a left join Data2 b on a.id=b.id group by a.id,a.date; quit;
Super Contributor
Super Contributor
Posts: 269

Re: merge data and check it there is data with in 7 days

Posted in reply to novinosrin
The question can be reworded as is there another encounter within 7 days as noted in data2 for the ids and dates in data 1.

I see that max(0<b.date-a.date<=7) is the magic, but I have no idea what is going on there and how that goes to a binary 0/1,
Super User
Posts: 2,050

Re: merge data and check it there is data with in 7 days

@HB  Good catch mate (I see that max(0<b.date-a.date<=7) is the magic) 

 

Basically any expression results to true of false in most if not all programming languages which in numbers is 1 or 0. True positive is <=7  in this case and False positive is anything otherwise. My python  prof at my college loves binary processing big time and encourages to effectively use it all the time when appropriate. Hope that helps

Super Contributor
Super Contributor
Posts: 269

Re: merge data and check it there is data with in 7 days

[ Edited ]
Posted in reply to novinosrin

@novinosrin

 

I get it now. So if there is a future date that is within 7 days, b.date- a.date will be between 0 and 7 and that expression will evaluate to true (1). The criteria has to be >0 to not match to itself. Slick.

Contributor
Posts: 42

Re: merge data and check it there is data with in 7 days

Posted in reply to novinosrin

would it be possible to tell if the encounter happened on the same day but with a different reps

for example in data1 ID 1 has encounter on 7jan2018 with sam but in data2 ID1 has encounter on 7jan2018 with sam and bob 

and i want that to equal to 1 as seen?

 

 

 

data Data1;
  input id date :date9. reps;
  format date date9.;
  datalines;1 07JAN2018 sam
2 28MAR2018 bob
3 14FEB2018 sally;

data Data2; 
input id date :date9.reps; format date date9.;  datalines; 
1 07JAN2018  sam
1 07JAN2018  bob
1 14FEB2018  sally
2 28MAR2018  sam 
2 04APR2018   bob
2 17APR2018    sally
3 14FEB2018   sam
3 18FEB2018   bob
3 27MAR2018   sally
;

  

Super User
Posts: 2,050

Re: merge data and check it there is data with in 7 days

Do you mean this?

 

data Data1;
  input id date :date9. reps $;
  format date date9.;
  datalines;
1 07JAN2018 sam
2 28MAR2018 bob
3 14FEB2018 sally
;

data Data2; 
input id date :date9. reps $; 
format date date9.; 
datalines; 
1 07JAN2018  sam
1 07JAN2018  bob
1 14FEB2018  sally
2 28MAR2018  sam 
2 04APR2018   bob
2 17APR2018    sally
3 14FEB2018   sam
3 18FEB2018   bob
3 27MAR2018   sally
;

proc sql;
create table want as
select a.*,max(a.reps ne b.reps and a.date=b.date) as seen
from Data1 a left join Data2 b
on a.id=b.id 
group by a.id,a.date,a.reps;
quit;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 217 views
  • 2 likes
  • 3 in conversation