topic Re: Identify first and second visit after index date in SAS Programming

Identify first and second visit after index date

CathyVI — Tue, 28 Feb 2023 20:05:37 GMT

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;

Re: Identify first and second visit after index date

Reeza — Tue, 28 Feb 2023 20:10:04 GMT

Can you include the expected output?

Re: Identify first and second visit after index date

PeterClemmensen — Tue, 28 Feb 2023 20:13:09 GMT

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

Re: Identify first and second visit after index date

Reeza — Tue, 28 Feb 2023 20:18:23 GMT

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

Re: Identify first and second visit after index date

Reeza — Tue, 28 Feb 2023 20:25:38 GMT

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;

Re: Identify first and second visit after index date

CathyVI — Wed, 01 Mar 2023 16:49:23 GMT

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

Re: Identify first and second visit after index date

Reeza — Wed, 01 Mar 2023 16:57:00 GMT

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.

Re: Identify first and second visit after index date

CathyVI — Wed, 01 Mar 2023 21:53:02 GMT

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

Re: Identify first and second visit after index date

Reeza — Wed, 01 Mar 2023 22:29:45 GMT

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

Re: Identify first and second visit after index date

CathyVI — Wed, 01 Mar 2023 22:42:54 GMT

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

Re: Identify first and second visit after index date

Reeza — Thu, 02 Mar 2023 16:52:49 GMT

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;