Code to identify consecutive and overlapping dates

[carmong]

I have a two part question (sample data attached)

I have a file with admission and discharge dates, some have multiple admissions and discharges.

What would be the best way to code to determine:

- if the multiple admission and discharge dates are consecutive (chronological) or not

- if the dates overlap (for example if a patients first admission date is 1/1/2022 and the discharge is 1/3/2022 but their second admission date is 1/2/2022 and the second discharge is 1/4/2022)

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software or policy. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values (especially dates).

Provide example data in the forum of data step code.

Also provide what you expect the output to look like.

[carmong]

An example of my data is as follows:

ID	ICU_AdmitDate	ICU_AdmitDate2	ICU_AdmitDate3	ICU_DischargeDate	ICU_DischargeDate2	ICU_DischargeDate3
1	4-Apr-21	10-Apr-21	.	7-Apr-21	10-Apr-21	.
2	18-Oct-21	25-Oct-21	5-Nov-21	25-Oct-21	5-Nov-21	27-Nov-21

[Patrick · Accepted solution]

What helps us help you is if you provide sample data in the form of a fully working "have" data step as done below. Then show us the desired result based on your sample data. 

It's easy enough to generate a "have" dataset from an Excel or text source using the SAS EG or SAS Studio import wizards (with embed data selected).

The Excel data you've provided appears to not have a single case with overlapping date ranges - so I had to make one up.

data work.have;
  infile datalines4
    dlm='|'
    truncover
    dsd 
    ;
  format
    ID                 best12.
    ICU_AdmitDate      date9.
    ICU_AdmitDate2     date9.
    ICU_AdmitDate3     date9.
    ICU_DischargeDate  date9.
    ICU_DischargeDate2 date9.
    ICU_DischargeDate3 date9. 
    ;
  input
    ID                  : 32.
    ICU_AdmitDate       : date9.
    ICU_AdmitDate2      : date9.
    ICU_AdmitDate3      : date9.
    ICU_DischargeDate   : date9.
    ICU_DischargeDate2  : date9.
    ICU_DischargeDate3  : date9.
    ;
  datalines4;
1|04APR2021|10APR2021|.|07APR2021|10APR2021|.
2|18OCT2021|25OCT2021|05NOV2021|25OCT2021|05NOV2021|27NOV2021
3|29JUL2021|.|.|10SEP2021|.|.
4|27JUN2020|.|.|14JUL2020|.|.
5|26DEC2020|.|.|28DEC2020|.|.
6|20JAN2021|21JAN2021|.|22JAN2021|02FEB2021|.
7|30MAR2020|.|.|11APR2020|.|.
8|23APR2021|.|.|25APR2021|.|.
9|17JAN2021|.|.|05FEB2021|.|.
10|29DEC2020|12JAN2021|.|29DEC2020|22JAN2021|.
;;;;

data want;
  overlap_flg=0;
  set have;
  array admit {*} ICU_AdmitDate ICU_AdmitDate2 ICU_AdmitDate3;
  array disch {*} ICU_DischargeDate ICU_DischargeDate2 ICU_DischargeDate3;

  do i=2 to dim(admit) while (not missing(admit[i]));
    if disch[i-1]>admit[i] then 
      do;
        overlap_flg=1;
        leave;
      end;
  end;
run;