Assessing health visits on a set schedule: counting consecutive events and missed occurrences

[hpauls2]

Hi Everyone,

Long time lurker, first time poster. I've been mulling over this problem for weeks and figured its finally time to ask for help. 🙏

I'm trying to assess whether health clinics we trained are completing patient visits on the following schedule:
"Period 1": at least 1 visit a week (every 7 days) for first 4 weeks (for a desired min total of 4 visits over 28 days)
"Period 2": Then, at least 1 visit a month (every 30 days) for the next 17 months (for a desired min total of 21 visits, one every 30 days, over months 2-18)

Some caveats:
- My code creates custom intervals in which each person's individual visits should occur (week1-week4, month1-month18)
- There can be multiple visits in a given week/month, but I am only interested in whether the minimum visit requirement was met (ignore multiple visits in a window);

- For period 1, I use the SAS week function to number the week of 52 that visit occurred in. For example, we should data in week 1, 2, 3, 4 consecutively, but sometimes we see 2, 4, or 1, 7, etc; these I need to count as the # of non compliant visits. I had used proc sort nodupkey to delete multiple visits on week but I can't do this for period 2 because the custom intervals/windows could miss a valid schedule, for example 8/1/2021+30days = 8/31/2021, which the month variable would read 8, 8 and seem non compliant based on this, but still a visit on 8/1/2021 and 8/31/2021 would be compliant since the 31st is the first day of their next window
- I do not have complete data, meaning visit cycles are not yet complete (someone may only be within their 2nd week of 4, or 15th month of 18)

- The unit of analysis in my output table should be PERSON, not visit

-The output tables (one for P1, one for P2) I want would have clinic in rows, and then three columns: # people who had all visits completed on schedule, # who had any missed visits, and total # of people

Here's some simplified dummy data and code from what I've got so far:

(SAS v9.4)

data long1;
 input id  clinic $	discharge_date	:mmddyy10. visit_date :mmddyy10.	week	month	nvisit	last	month_num	current_month	count; 
 format discharge_date	visit_date mmddyy10.;
cards;
1	a	10/16/2022	10/16/2022	41	10	1	.	1	1	1
1	a	10/16/2022	10/21/2022	42	10	2	.	1	1	1
1	a	10/16/2022	10/22/2022	42	10	3	1	1	1	1
2	a	8/12/2022	8/20/2022	33	8	1	.	1	3	1
2	a	8/12/2022	9/4/2022	35	9	2	.	1	3	1
2	a	8/12/2022	9/11/2022	36	9	3	.	1	3	1
2	a	8/12/2022	9/17/2022	37	9	4	.	2	3	1
2	a	8/12/2022	11/19/2022	46	11	5	1	4	3	1
3	a	7/16/2022	7/17/2022	28	7	1	.	1	4	1
3	a	7/16/2022	8/14/2022	32	8	2	1	1	4	1
4	b	9/3/2022	9/11/2022	36	9	1	.	1	3	1
4	b	9/3/2022	9/17/2022	37	9	2	.	1	3	1
4	b	9/3/2022	9/24/2022	38	9	3	.	1	3	1
4	b	9/3/2022	10/2/2022	39	9	4	1	1	3	1
5	b	7/17/2022	7/24/2022	29	7	1	.	1	4	1
5	b	7/17/2022	7/31/2022	30	7	2	.	1	4	1
5	b	7/17/2022	8/5/2022	31	8	3	.	1	4	1
5	b	7/17/2022	8/7/2022	31	8	4	.	1	4	1
5	b	7/17/2022	8/14/2022	32	8	5	.	1	4	1
5	b	7/17/2022	8/21/2022	33	8	6	.	2	4	1
5	b	7/17/2022	9/1/2022	35	8	7	.	2	4	1
5	b	7/17/2022	9/8/2022	36	9	8	.	2	4	1
5	b	7/17/2022	9/11/2022	36	9	9	.	2	4	1
5	b	7/17/2022	9/18/2022	37	9	10	.	3	4	1
5	b	7/17/2022	10/8/2022	40	10	11	.	3	4	1
5	b	7/17/2022	10/20/2022	42	10	12	1	4	4	1
; run; 
proc print noobs; run; 

data long2; 
set long1; 
by id visit_date; 
week = week(visit_date); 
month = month(visit_date); 
/*PERIOD 1 (first four weeks): create custom week (7day) intevals (end date that person's individual 1st,2nd,3rd,4th week post-discharge)*/
week1 = discharge_date +7;
week2 = discharge_date +14;
week3 = discharge_date +21;
week4 = discharge_date +28;
/*PERIOD 2 (months 2-18): create custom month (30day) intevals */
month1 = discharge_date +30;
month2 = discharge_date +60;
month3 = discharge_date +90;
month4 = discharge_date +120;
month5 = discharge_date +150;
month6 = discharge_date +180;
month7 = discharge_date +210;
month8 = discharge_date +240;
month9 = discharge_date +270;
month10 = discharge_date +300;
month11 = discharge_date +330;
month12 = discharge_date +360;
month13 = discharge_date +390;
month14 = discharge_date +420;
month15 = discharge_date +450;
month16 = discharge_date +480;
month17 = discharge_date +510;
month18 = discharge_date +540;

/*renamed fac_month to comm_month_num*/
if visit_date <= month1 then month_num = 1; 
if visit_date > month1 and visit_date <= month2 then month_num = 2; 
if visit_date > month2 and visit_date <= month3 then month_num = 3; 
/*... */
if visit_date > month17 and visit_date <= month18 then month_num = 18; 
label month_num = "patients month number post-discharge (# of 30 day increments since discharge"; 

today = today();
if today >month1  then current_month = 1; 
if today >month2  then current_month = 2; 
/*...*/
if today >month18 then current_month = 18; 
label current_month = "Current month (of 18) of post-discharge visit cycle based on patients' last visit date "; 

/*create count varoiable for summary tables below to count*/
count = 1; 
run; 

/*use proc sort nodupkey to remove multiple visits in a week, month*/
/*data long3; set long2;  proc sort nodupkey ; by id week; run; */


/*output tables-- do not currently distingusih between Period 1&2*/
proc means data = long2 ; class clinic month_num; var count ;
output out=means(drop= _freq_) sum= / autoname;
run;

data monthly_counts (drop=_TYPE_); set means; where _TYPE_ = 3; rename count_Sum = count;  run;

[ErikLund_Jensen]

Hi @hpauls2 

Here is a quite different approach, which I think is simpler, because I found id hard to underestand and fix your code.

The principle is that for each patient, all expected visit weeks and months are generated. As is,this provides a full set of 4 weeks and 18 months for each patient, but it would be easy to expected visits in the future based on date of discharge.

Then the expected visits are joined with the actual visits expressed as weeks and motths, and finally the missing weekly/monthly visits are extracted for reporting.

It is not a complete solution, only a mechanism to identify missing visits, so It needs some improvement or post-processing to give your final report data, e.g. include clinic as key variable, pull more variables through the visitbase-dataset and reform output.

* Create test data;
data patientvisits;
 input id  clinic $	discharge_date	:mmddyy10. visit_date :mmddyy10.	week	month	nvisit	last	month_num	current_month	count; 
 format discharge_date	visit_date mmddyy10.;
cards;
1	a	10/16/2022	10/16/2022	41	10	1	.	1	1	1
1	a	10/16/2022	10/21/2022	42	10	2	.	1	1	1
1	a	10/16/2022	10/22/2022	42	10	3	1	1	1	1
2	a	8/12/2022	8/20/2022	33	8	1	.	1	3	1
2	a	8/12/2022	9/4/2022	35	9	2	.	1	3	1
2	a	8/12/2022	9/11/2022	36	9	3	.	1	3	1
2	a	8/12/2022	9/17/2022	37	9	4	.	2	3	1
2	a	8/12/2022	11/19/2022	46	11	5	1	4	3	1
3	a	7/16/2022	7/17/2022	28	7	1	.	1	4	1
3	a	7/16/2022	8/14/2022	32	8	2	1	1	4	1
4	b	9/3/2022	9/11/2022	36	9	1	.	1	3	1
4	b	9/3/2022	9/17/2022	37	9	2	.	1	3	1
4	b	9/3/2022	9/24/2022	38	9	3	.	1	3	1
4	b	9/3/2022	10/2/2022	39	9	4	1	1	3	1
5	b	7/17/2022	7/24/2022	29	7	1	.	1	4	1
5	b	7/17/2022	7/31/2022	30	7	2	.	1	4	1
5	b	7/17/2022	8/5/2022	31	8	3	.	1	4	1
5	b	7/17/2022	8/7/2022	31	8	4	.	1	4	1
5	b	7/17/2022	8/14/2022	32	8	5	.	1	4	1
5	b	7/17/2022	8/21/2022	33	8	6	.	2	4	1
5	b	7/17/2022	9/1/2022	35	8	7	.	2	4	1
5	b	7/17/2022	9/8/2022	36	9	8	.	2	4	1
5	b	7/17/2022	9/11/2022	36	9	9	.	2	4	1
5	b	7/17/2022	9/18/2022	37	9	10	.	3	4	1
5	b	7/17/2022	10/8/2022	40	10	11	.	3	4	1
5	b	7/17/2022	10/20/2022	42	10	12	1	4	4	1
; 
run; 

* Sort - just as precaution;
proc sort data=patientvisits;
  by id visit_date;
run;

* Create all expected visits per id;
data visitbase;
  set patientvisits;
  by id;
  length visit_type $5;
  if first.id then do;
    visit_type = 'week';
    do visit_expected = 1 to 4;
      output;
    end; 
    visit_type = 'month';
    do visit_expected = 1 to 18;
      output;
    end; 
  end;
run;

* Extract actual visits from input;
data visitfact;
  set patientvisits;
  length visit_type $5;
  visit_type = 'week';
  visit_actual = int(((visit_date - discharge_date) / 7) + 1); 
  output;
  visit_type = 'month';
  visit_actual = int(((visit_date - discharge_date) / 30) + 1); 
  output;
run;

* Merge expected visits and actual visits - discard visits not within expectations;
proc sql;
  create table visit_control as
    select distinct
      coalesce(a.id, b.id) as id,
      coalesce(a.discharge_date, b.discharge_date) as discharge_date format=mmddyy10.,
      coalesce(a.visit_type, b.visit_type) as visit_type,
      a.visit_expected,
      b.visit_actual
    from visitbase as a
    left join visitfact as b
    on 
      a.id = b.id and
      a.discharge_date = b.discharge_date and
      a.visit_type = b.visit_type and
      a.visit_expected = b.visit_actual
    order by 
      id, 
      visit_type desc, 
      visit_expected
  ;
quit;

* report missing visits - distinct;
proc sql;
  create table result as
    select distinct
      id,
      discharge_date,
      visit_type,
      visit_expected as visit_missing
    from visit_control
    where visit_actual = . 
    order by 
      id, 
      visit_type desc, 
      visit_expected
  ;
quit;

[hpauls2]

/*prep visit_control which has all expected visits n=22*/
data vc2; set visit_control; 
if visit_actual ne . then do; 
	visit=1; end;
if visit_type = 'week' then period = 1; 
if visit_type = 'month' then period = 2; run; 


/*merge with lastvisit/summary*/
proc sort data=lastvisit; by id; run; 
proc sort data=vc2; by id; run; 
data summary; 
merge
vc2 
lastvisit
; by id;  
if last.id then last=1; 
/*Date Differences: calculate days between LAST VISIT and DISCHARGE DATE and TODAY*/
days1=intck('day', discharge_date, last_visit_date); label days1='Days between discharge_date and last_visit_date';
days2=intck('day', discharge_date, today); label days2='Days since discharge';
days3=intck('day', last_visit_date, today); label days3='Days since last visit';
/*flag missed visits, inverse of visit_actual*/
if visit=. then missed_visit = 1; 
run; 

data p1_complete; set test; where period=1 and days1 >=28; run; 
data p1_incomplete; set test; where period=1 and days1<28; run; 
data p2; set test; where period=2; 
if nmonth<visit_expected then drop_future= 1; run; 

Thank you, @ErikLund_Jensen! Yours is an elegant and simple program compared to where had been going with the code I shared. I am a total novice to SQL/Proc Sql but the output tables it produces accomplish what has escaped me, creating an data row for each expected visit (4 +18=22 rows per person). 

Your program accomplishes all of the heavy lifting other than counting and producing the summary table of # of patients with  a) all completed visits by period and b) not all visits completed by period. It would also be handy to show as percentage of visits completed, by patient; but the denominator changes depending on where they are in the cycle for P1 or P2.

To finish the program, I merged in a variable I created called "last_visit_date" which I'm trying to use to denote complete/incomplete visit cycles for period 1 and period 2 by cutting out the future expected visits, which aren't valid to be counted yet. For example, someone is discharged 12/1/2022 and is seen 12/7, 12/14 but the window for the 3rd and 4th week in period 1 have not yet closed, meaning this person has completed 2/2 (100%) of expected visits, not 2 of 4. Some more scenarios: a) p1 4actual/4expected = 100%; b) p1 2/3 = 66%; c) p2 5/7= ~71%; etc. For  example b, they had 2 visits in a 21 day period when they should have had 3. This could have been week 1&3, 1&2, or 2&3 but regardless they had only 2 visits of 3 expected/required.

If I can finish by getting summary info for everyone, I plan to use proc tabulate to make a pretty table.

Thanks again!

Heather