exclude patient who had >=1 claim for drug during the 90 days prior to or on the index date

[lulu3]

Hi Expert,

I wanted to exclude patients who have more than one claim of the drug during the 90 dyas prior to or on the index date.

For example:

data have;
input id $ drug $ indexdate mmddyy10.;
format indexdate mmddyy10.;
datalines;
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
3 A 03/21/2016
3 B 03/21/2016
4 C 07/24/2016
4 B 07/30/2016
5 A 02/23/2016
5 C 05/12/2016

;
run;

Data I want:

1 A 10/07/2016

1 B 06/21/2016

2 C 07/27/2017

2 A 09/09/2015

I wanted to exclude id 3 4 5 because they had two drug claims within 90 days.

Thanks!

[ballardw]

You don't show any dates other than Indexdate, so I am not sure what you expect to compare with the values of indexdate. OR you need to define how we can tell that a specific date in the example is supposed to be considered an "index date".

[lulu3]

Yes, I wanted to compare with the values of indexdate for each subject.

[ballardw]

I repeat: HOW do we know which date is the "index date".

[lulu3]

Sorry for the confusion. Please use the earliest drug date as the indexdate for each subject.

[mkeintz · Accepted solution]

You need to read each id twice:

1. The first time determines the earliest and 2nd earliest dates.
2. The second pass output record for any id with 2nd earliest more than 90 days after the earliest

Each mention of HAVE in the SET statement represents one pass of the records for a given ID:

data have;
input id $ drug $ indexdate mmddyy10.;
format indexdate mmddyy10.;
datalines;
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
3 A 03/21/2016
3 B 03/21/2016
4 C 07/24/2016
4 B 07/30/2016
5 A 02/23/2016
5 C 05/12/2016
;
run;

data want (drop=_:);
  retain _mindate1 _mindate2;
  set have (in=firstpass) have (in=secondpass);
  by id;

  if first.id then call missing(of _mindate:);

  if firstpass=1 then do;
    if indexdate=min(indexdate,_mindate1) then do;
      _mindate2=_mindate1;
      _mindate1=indexdate;
    end;
    else _mindate2=min(_mindate2,indexdate);
  end;

  if secondpass=1 and _mindate2-_mindate1>90;
run;

And THANK YOU for posting you data set in almost perfect format.  ("Perfect" would have been if you inserted it in a SAS Code popup (use the "running man" icon, but your code was directly copyable and usable.

[lulu3]

Thank you for your solution!