How to find difference between multiple dates?

[raj23]

I have the following data that lists each person's hospital admission dates during a pre-defined study period.

patient_id	admit_dt
1	1/3/2012
1	11/21/2017
1	10/2/2018
1	10/3/2018
1	11/12/2018
1	8/21/2019
1	3/13/2021
1	5/2/2021

I want to find if a person has at least 2 hospital admissions within 2 years of each other. So far, I have the following code, but it only compares each admission to the earliest admission date.

data data_out;
set data_in;
retain first_admit_dt;
by patient_id;
if first.patient_id then do;
first_admit_dt=admit_dt;
end;
else do;
ddiff=admit_dt-first_admit_dt;
if ddiff<(365*2) then admit_flag=1;
end;
format first_admit_dt mmddyy10.;
drop first_admit_dt;
run;

This is the output I'm getting:

patient_id	admit_dt	ddiff	admit_flag
1	1/3/2012	.	.
1	11/21/2017	2149	.
1	10/2/2018	2464	.
1	10/3/2018	2465	.
1	11/12/2018	2505	.
1	8/21/2019	2787	.
1	3/13/2021	3357	.
1	5/2/2021	3407	.

However, I want to see if any of the admission dates are within 2 years of each other, not just compared to the earliest admission date. From the example above, 11/21/2017 is not within 2 years from the first admit date of 1/3/2012. However, the admit date on 10/2/2018 is within 2 years of 11/21/2017, so this person would be considered to have at least 2 admissions within 2 year of each other.

Thank you

[Ksharp]

Assuming I understood what you mean.

data have;
input patient_id	admit_dt : mmddyy10.;
format admit_dt  mmddyy10.;
cards;
1	1/3/2012
1	11/21/2017
1	10/2/2018
1	10/3/2018
1	11/12/2018
1	8/21/2019
1	3/13/2021
1	5/2/2021
;

proc sql;
create table want as
select *,
(select count(*) from have 
where patient_id=a.patient_id and admit_dt between a.admit_dt and intnx('year',a.admit_dt,2,'s')
) > 1 as admit_flag
 from have as a;
quit;

[mkeintz]

Do you want to compare each ADMIT_DT to all other admission dates, or only to earlier admission dates?

Here is code that creates two flags, one to indicate whether the prior admission is within two years, and one to indicate whether the next admission is within two years.

This assumes data are sorted by admit_dt within patient_id.

data have;
input patient_id	admit_dt : mmddyy10.;
format admit_dt  mmddyy10.;
cards;
1	1/3/2012
1	11/21/2017
1	10/2/2018
1	10/3/2018
1	11/12/2018
1	8/21/2019
1	3/13/2021
1	5/2/2021
;

data want (drop=nxt_:);
  set have;
  by patient_id;

  nxt_admit=.;
  if eod2=0 then set have (firstobs=2 keep=admit_dt rename=(admit_dt=nxt_admit)) end=eod2;


  prior_admit_flag=first.patient_id=0 and intnx('year',admit_dt,-2,'s')<=lag(admit_dt);
  later_admit_flag=last.patient_id=0  and intnx('year',admit_dt,2,'s')>=nxt_admit;
run;

[mkeintz]

You may want more than a flag variable.  Here is code to count the number of earlier admission dates within 2 years (nprior), and the number of later admission dates within two years (nafter):

data counts (drop=_:);
  set have (in=firstpass) have (in=secondpass);
  by patient_id;

  array dates{0:50} _temporary_;

  if first.patient_id then call missing(of dates{*},_n1,_n2);

  _n1+firstpass;
  if firstpass then dates{_n1}=admit_dt;

  if secondpass;
  _n2+1;

  _cutoff=intnx('year',admit_dt,-2,'s');  
  do nprior=0 by 1 until (dates{_n2-1-nprior}<_cutoff);
  end;

  _cutoff=intnx('year',admit_dt,+2,'s');
  do nafter=0 by 1 until (dates{_n2+1+nafter}>_cutoff);
    if dates{_n2+1+nafter}=. then leave;
  end;

run;

This assumes data are sorted by admit_dt within patient_id.  Also make sure that the lower bound of the DATES array is zero, and the upper bound is at least 1 more than the maximum number of single-patient admissions in dataset HAVE.