## Days to last patient visit

Solved
Regular Contributor
Posts: 161

# 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_id visit_date A 5/17/2012 A 5/27/2012 B 11/4/2013 C 2/4/2014 D 5/10/2011 E 6/4/2013

HAVE 2

 patient_id doctor_id telephone_consultation_date A D1 5/19/2012 A D2 5/24/2012 A D2 B D7 B D7 C D6 C D6

WANT

 patient_id doctor_id telephone_consultation_date days to last visit days to next visit A D1 5/19/2012 2 8 A D2 5/24/2012 7 3 A D2 B D7 B D7 C D6 C D6

Accepted Solutions
Solution
‎05-05-2014 06:59 AM
Super User
Posts: 10,784

## 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

All Replies
Super User
Posts: 23,771

## Re: Days to last patient visit

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: 10,784

## 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: 10,784

## 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 and locked.