Days to last patient visit

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

Days to last patient visit

Dear All:

Could you please how to calculate the number of days from the date the patient last visited to a given date and the number of days to next visits?

HAVE 1

patient_idvisit_date
A5/17/2012
A5/27/2012
B11/4/2013
C2/4/2014
D5/10/2011
E6/4/2013

HAVE 2

patient_iddoctor_idtelephone_consultation_date
AD15/19/2012
AD25/24/2012
AD2
BD7
BD7
CD6
CD6

WANT

patient_iddoctor_idtelephone_consultation_datedays to last visitdays to next visit
AD15/19/201228
AD25/24/201273
AD2
BD7
BD7
CD6
CD6

Accepted Solutions
Solution
‎05-05-2014 06:59 AM
Super User
Posts: 9,681

Re: Days to last patient visit

data HAVE1;
input patient_id     $ visit_date :mmddyy12.;
format visit_date mmddyy10.;
cards;
A     5/17/2012
A     5/27/2012
B     11/4/2013
C     2/4/2014
D     5/10/2011
E     6/4/2013
;
run;
 
data HAVE2;
input patient_id $     doctor_id $     telephone_consultation_date :mmddyy12.;
format telephone_consultation_date mmddyy10.;
cards;
A     D1     5/19/2012
A     D2     5/24/2012
A     D2 5/28/2012
B     D7 10/24/2013
B     D7 12/24/2013
C     D6 1/24/2012
C     D6 1/25/2012
;
run;
data want(drop=i j visit_date);
 if _n_ eq 1 then do;
  if 0 then set have1;
  declare hash h(dataset:'have1');
   h.definekey('patient_id','visit_date');
   h.definedone();
 end;
set have2;
do i=1 to 4000;
 visit_date=telephone_consultation_date-i;
 if h.check()=0 then leave;
end;
days_to_last_visit=ifn( i=4001,9999,i);
do j=1 to 4000;
 visit_date=telephone_consultation_date+j;
 if h.check()=0 then leave;
end;
days_to_next_visit=ifn( j=4001,9999,j);
run;

Xia Keshan

View solution in original post


All Replies
Super User
Posts: 17,831

Re: Days to last patient visit

How big are your datasets?

Regular Contributor
Posts: 161

Re: Days to last patient visit

Hi, Reeza:

Your concern is quite insightful.  The data is very big, about 100 GB in total.  The look up table is around 50 MB.  So Ksharp's implementation of hash table would be absolutely crucial here.

Thanks, again!

Solution
‎05-05-2014 06:59 AM
Super User
Posts: 9,681

Re: Days to last patient visit

data HAVE1;
input patient_id     $ visit_date :mmddyy12.;
format visit_date mmddyy10.;
cards;
A     5/17/2012
A     5/27/2012
B     11/4/2013
C     2/4/2014
D     5/10/2011
E     6/4/2013
;
run;
 
data HAVE2;
input patient_id $     doctor_id $     telephone_consultation_date :mmddyy12.;
format telephone_consultation_date mmddyy10.;
cards;
A     D1     5/19/2012
A     D2     5/24/2012
A     D2 5/28/2012
B     D7 10/24/2013
B     D7 12/24/2013
C     D6 1/24/2012
C     D6 1/25/2012
;
run;
data want(drop=i j visit_date);
 if _n_ eq 1 then do;
  if 0 then set have1;
  declare hash h(dataset:'have1');
   h.definekey('patient_id','visit_date');
   h.definedone();
 end;
set have2;
do i=1 to 4000;
 visit_date=telephone_consultation_date-i;
 if h.check()=0 then leave;
end;
days_to_last_visit=ifn( i=4001,9999,i);
do j=1 to 4000;
 visit_date=telephone_consultation_date+j;
 if h.check()=0 then leave;
end;
days_to_next_visit=ifn( j=4001,9999,j);
run;

Xia Keshan

Regular Contributor
Posts: 161

Re: Days to last patient visit

Hi, Ksharp:

This code works great.  But it use the hash objects that i don't really understand.  Could you explain a bit of the logic of the code?

By the way, what if I want to set the value to zero for both days to last visit and days to next visit on the day of visit when the patient makes the call on the same day he is out of the hospital?

Could you also tell me the role of 4000?  If I know that the patient for sure will visit within one year of making a phone call, I can set to 365, right?

Many thanks!

Super User
Posts: 9,681

Re: Days to last patient visit

I think I could get it by SQL. But you said your dataset is 100 G, I stop immediately . I can't image in Pharmacy field ,there will be so large table .

About Hash Table , it is a long story to tell , you should dig it in more .

Here , I am checking if  telephone_consultation_date is in Have1 from past firstly

do i=1 to 4000;

visit_date=telephone_consultation_date-i;

if h.check()=0 then leave;

end;

after checking 4000 days past, if there is no match in Have1, I set it be 9999, because there is no matched one in Have1 .

then , vice versa for future days .

"Could you also tell me the role of 4000?"

It is a threshold . after checking 4000 days before or after  telephone_consultation_date ,we still don't find matched one then I assume there is no matched one in Have1.

" If I know that the patient for sure will visit within one year of making a phone call, I can set to 365, right?"

No. 4000 is only a threshold to judge if there is a matched one in Have1.You can set it 40000 or 8000 or whatever , as long as you make sure there could not be  a patient to make a phone call at 40000 or 8000 or whatever.

"By the way, what if I want to set the value to zero for both days to last visit and days to next visit on the day of visit when the patient makes the call on the same day he is out of the hospital?"

Oh, you remind me I missed one thing. Here is :

do i=0 to 4000;

............

do j=0 to 4000;

.........

Xia Keshan

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 313 views
  • 3 likes
  • 3 in conversation