Select the onset date of a disease based on the number of visits

Question

I have data on physician visits for an infection. Individuals can have 1+ visits. Clinicians consider an individual is infected if he has more than one visit within a year. Then, the date of the first visit is considered as infection onset.

For example, ids 1, 2, and 3 have the following visits:

Data have;
INPUT row ID date;
CARDS;
1 1 2017-03-22
2 1 2017-04-26
3 1 2018-02-20
4 1 2018-04-07
5 1 2018-04-16
6 2 2014-01-15
7 2 2014-06-23
8 2 2014-07-23
9 2 2015-01-14
10 3 2018-01-22
11 3 2019-05-03
;
run;

Based on the clinical definition of the infection, I want these dates:

row	ID	date
1	1	2017-03-22
4	1	2018-04-07
6	2	2014-01-15

The first date of ID=1 is selected because there is 2+ visits within the a year. All visits after the first visit and within a year from the first visit are skipped (row 2 and 3). The second date is row 4, which is more than one year apart from the first visit and there is another visit within a year after.

For ID=2, we select only the first date and skip all the next visits within a year.

For ID=3, we don’t select any date because there is no more than one visit within a year.

This is very complex in proc SQL and might not be doable. Use a data step. — Gordon Linoff, Jul 13 '21 at 11:26

score 1 · Accepted Answer · answered Jul 13 '21 at 22:10

Try following approach, you might need to tweak a bit when you work on actual full data.

  Data have;
  INPUT row:4. ID:4. date yymmdd10.;
  year=year(date);
  format date yymmdd10.;
  CARDS;
  1 1 2017-03-22
  2 1 2017-04-26
  3 1 2018-02-20
  4 1 2018-04-07
  5 1 2018-04-16
  6 2 2014-01-15
  7 2 2014-06-23
  8 2 2014-07-23
  9 2 2015-01-14
  10 3 2018-01-22
  11 3 2019-05-03
  ;
  run;
  
  /*keep the first date per id*/
  data first_visit;
   set have;
    by id;
    if first.id;
  run;
  
  /*find the year difference for each subsequent date with first date*/
  proc sql;
  create table visits1
  as
  select a.*, 
  int(abs(yrdif(b.date,a.date,'ACT/365'))) as date_diff_first
  from have a
  inner join first_visit b
  on a.id=b.id;
  quit;
  
  /*part1 - find the first symptom onset date where we have multiple visits per year*/
  proc sql;
  create table part1_0
  as
  select distinct a.*
  from visits1 a
  inner join visits1 b
  on a.id=b.id
  and a.date<b.date
  and a.row+1=b.row
  and a.year=b.year
  and a.date_diff_first=0 and b.date_diff_first=0;
  quit;
  
  data part1;
   set part1_0;
   by id;
   if first.id;
  run;
  
  /*part2 - Now find other symptom onset date for same id in another year 1 year apart*/
  proc sql;
  create table part2_0
  as
  select distinct a.row, a.id, a.date
  from visits1 a
  inner join part1 b
  on a.id=b.id
  inner join visits1 c
  on a.id=c.id
  and a.date>b.date
  and a.year<>b.year
  and a.date_diff_first=1 and b.date_diff_first=0
  and a.row+1=c.row;
  quit;
  
  data part2;
   set part2_0;
   by id;
   if first.id;
  run;
  
  /*combine both*/
  data final;
   set part1 part2;
   keep row id date;
  run;
  
  proc sort data=final; by row id date; run;

Select the onset date of a disease based on the number of visits

1 Answers1