Identify first and second visit after index date

[CathyVI]

Hello,

I am working on a data that I combined data and would like to identify the first visit date and second visit (90 days apart) from the index date. I have created a dummy data for assistance. 

I have created a index date, visit date and follow_up date (index date+365).

I want to find the first visit date after the index date for each patient then find the second visit which will be 90 days from the first visit.

this is my attempt but am not getting it. I will appreciate any help with the coding.

data have;
input pt_id $ Name $ index_dt :mmddyy10. followup_365 :mmddyy10. visit_dt :mmddyy10.;
format   followup_365 index_dt visit_dt mmddyy10.;
datalines;

Obs pt_id Name index_dt visit_dt followup_365
1 3 Jones 03/08/2019 05/08/2019 03/07/2020
2 3 Jones 03/08/2019 02/19/2020 03/07/2020
3 3 Jones 03/08/2019 09/08/2020 03/07/2020
4 1 Smith 04/30/2019 04/30/2019 04/29/2020
5 1 Smith 04/30/2019 05/03/2020 04/29/2020
6 1 Smith 04/30/2019 07/23/2020 04/29/2020
7 1 Smith 04/30/2019 12/30/2020 04/29/2020
8 2 Williams 03/05/2020 02/05/2020 03/05/2021
9 2 Williams 03/05/2020 04/05/2020 03/05/2021
10 2 Williams 03/05/2020 10/15/2020 03/05/2021

;

run;

This is my two attempts but not working

proc sql;
create table chc_master as
select pt_id, Name, index_dt,followup_365, visit_dt, min(visit_dt) as first_visit format mmddyy10.
from have;
quit;

data chc_master;
set have;
if first.pt_id then do;
if first.index_dt ne . & first.visit_dt ne . then first_visit=1;
else first.visit=0;
end;
run;

[Reeza]

Can you include the expected output?

[PeterClemmensen]

@CathyVI , your data step should rather be like below. 

Given this data, what does your desired result look like?

data have;
input pt_id $ Name :$10. (index_dt followup_365 visit_dt) (:mmddyy10.);
format   followup_365 index_dt visit_dt mmddyy10.;
datalines;
3 Jones    03/08/2019 05/08/2019 03/07/2020 
3 Jones    03/08/2019 02/19/2020 03/07/2020 
3 Jones    03/08/2019 09/08/2020 03/07/2020 
1 Smith    04/30/2019 04/30/2019 04/29/2020 
1 Smith    04/30/2019 05/03/2020 04/29/2020 
1 Smith    04/30/2019 07/23/2020 04/29/2020 
1 Smith    04/30/2019 12/30/2020 04/29/2020 
2 Williams 03/05/2020 02/05/2020 03/05/2021 
2 Williams 03/05/2020 04/05/2020 03/05/2021 
2 Williams 03/05/2020 10/15/2020 03/05/2021 
;

[Reeza]

I think followup_365 and visit_dt are flipped based on the column headers in the data.

[Reeza]

This should get you closer:

data have;
input pt_id $ Name $ index_dt :mmddyy10.  visit_dt :mmddyy10. followup_365 :mmddyy10.;
format   followup_365 index_dt visit_dt mmddyy10.;
datalines;
3 Jones 03/08/2019 05/08/2019 03/07/2020
3 Jones 03/08/2019 02/19/2020 03/07/2020
3 Jones 03/08/2019 09/08/2020 03/07/2020
1 Smith 04/30/2019 04/30/2019 04/29/2020
1 Smith 04/30/2019 05/03/2020 04/29/2020
1 Smith 04/30/2019 07/23/2020 04/29/2020
1 Smith 04/30/2019 12/30/2020 04/29/2020
2 Williams 03/05/2020 02/05/2020 03/05/2021
2 Williams 03/05/2020 04/05/2020 03/05/2021
2 Williams 03/05/2020 10/15/2020 03/05/2021

;

run;

 

proc sort data=have;
by pt_id visit_dt;
run;


data want;
set have;
by pt_id;
retain visit_num;
if first.pt_id then call missing(visit_num);

if visit_dt>index_dt and not missing(visit_num) then visit_num+1;
else if visit_dt>index_dt then visit_num = 1;



run;

[CathyVI]

@Reeza @PeterClemmensen Thank you for your responses. This is what I expected in my output.

For example, Jones first visit is 05/08/2019 and if the time between the first and second visit is more than 90days from the first visit then second visit =1. This will be my retention in care outcome because it met our criteria, if not then second visit=0.

Expected output

Obs   pt_id    Name      index_dt             visit_dt           followup_365     first_visit           second_visit
1         3        Jones      03/08/2019        05/08/2019     03/07/2020        05/08/2019                 0
2         3        Jones      03/08/2019        02/19/2020     03/07/2020        05/08/2019                 1
3         3        Jones      03/08/2019        09/08/2020     03/07/2020        05/08/2019                 1   
4         1        Smith      04/30/2019        04/30/2019     04/29/2020        04/30/2019                 0 
5         1        Smith      04/30/2019        05/03/2019     04/29/2020        04/30/2019                 0
6         1        Smith      04/30/2019        07/23/2020     04/29/2020        04/30/2019                 1
7         1        Smith      04/30/2019        12/30/2020     04/29/2020        04/30/2019                 1
8         2        Williams  03/05/2020        02/05/2020     03/05/2021        02/05/2020                 0
9         2        Williams  03/05/2020        04/05/2020     03/05/2021        02/05/2020                 0
10       2        Williams  03/05/2020       10/15/2020      03/05/2021        02/05/2020                 1

Thank you again, looking forward to your responses.

[Reeza]

For Williams, the first visit is February 5, 2020 as your dates are mmddyy according to your post.

The Index date is May 5, 2020. Your initial post states the first visit must be after the index date, so shouldn't the first date be the 04/05/2020, ie April 5, 2020. 

[CathyVI]

@Reeza @PeterClemmensen Am sorry, it a typo. You are right the first visit should be 4/5/2020, which is the first visit after the index date. How do I get this first visit and 90 days after first visit?

[Reeza]

Can you please update the output? Does the date need to be on all lines? If so, that does change how it's done significantly (adds extra steps).

[CathyVI]

@Reeza The dates does not have to be in all but I think since I need first visit date + 90 days to identify the second visit date,  i will need first date to be in dates. 

I was trying my hands on this code but I don't know how to make first visit date in to date.

What am i missing here?

data want;
set have;
if (visit_dt)>index_dt then first_visit=1;
else first_visit=0;
run;

[Reeza]

Ok, results don't match your sample output but I think your sample is wrong. 

See if this works to meet your stated requirements. 

First visit is after index date (should this be >=?)
Second visit is 90 days or more after first visit

proc sort data=have;
by pt_id visit_dt;
run;


data want;
set have;
by pt_id;
retain  first_visit;
if first.pt_id then call missing(first_visit);

second_visit = 0;


if visit_dt>index_dt and missing(first_visit) then first_visit=visit_dt;
else if not missing(first_visit) and visit_dt -first_visit >=90 then second_visit=1;

format first_visit yymmdd10.;

run;