topic Re: merge data and check it there is data with in 7 days in SAS Enterprise Guide

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

hk2013 — Wed, 25 Apr 2018 19:23:45 GMT

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

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

novinosrin — Wed, 25 Apr 2018 19:53:54 GMT

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;

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

HB — Wed, 25 Apr 2018 20:11:59 GMT

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,

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

novinosrin — Wed, 25 Apr 2018 20:16:36 GMT

@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

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

HB — Wed, 25 Apr 2018 20:24:52 GMT

@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.

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

hk2013 — Wed, 25 Apr 2018 21:37:17 GMT

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
;

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

novinosrin — Wed, 25 Apr 2018 21:55:50 GMT

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;