topic Re: Delete observations in a group by dates? in SAS Programming

Delete observations in a group by dates?

Doyinsola — Tue, 02 Mar 2021 19:27:38 GMT

I have the following data:

Data sample;
	input id $ (diagnosis_date) (:mmddyy10.) date_diff1 date_diff2;
	format diagnosis_date date9.;
	datalines;
1 06/23/2007 . .
1 07/06/2007 13 13
1 09/26/2007 95 82
1 10/02/2007 101 6
2 04/07/2007 . .
2 04/28/2007 21 21
2 07/17/2007 100 79
2 08/05/2007 120 20
2 02/22/2008 220 120
;
run;

Each row is an event (a disease diagnosis). I would like to keep the row with the index diagnosis and remove events that are within 90 days of the index and also within 90 days of each other. I have created two variables to try to help myself with this.date_diff1 is the number of days between the event and the index diagnosis; date_diff2 is the number of days between an event and the previous event before it. This is the dataset I am hoping to end up with.

Re: Delete observations in a group by dates?

PhilC — Tue, 02 Mar 2021 21:50:48 GMT

I would word my solution, which is what I think you mean, like this . This removes "events that are within 90 days of the index and then within 90 days of each other, thereafter."

A SAS loop like this is called a "Do-Witlock" loop.

data want;
  do until (last.ID);
    set sample ;
      by ID diagnosis_date;
    if first.ID 
      then _next_date=diagnosis_date;
    if _next_date<=diagnosis_date then do;
      output;
      _next_date=diagnosis_date+89;
    end;
  end;
  drop _:;
run;

Re: Delete observations in a group by dates?

ChrisNZ — Wed, 03 Mar 2021 02:13:09 GMT

Like this?

data WANT; 
  set SAMPLE;
  by ID;
  retain REF_DATE;
  if first.ID | (ID=lag(ID) and DIAGNOSIS_DATE-REF_DATE > 90) then do;
    output;
    REF_DATE=DIAGNOSIS_DATE;
  end;
run;

ID	DIAGNOSIS_DATE	DATE_DIFF1	DATE_DIFF2
1	23JUN2007	.	.
1	26SEP2007	95	82
2	07APR2007	.	.
2	17JUL2007	100	79
2	22FEB2008	220	120

Re: Delete observations in a group by dates?

Doyinsola — Wed, 03 Mar 2021 15:15:45 GMT

This seems to work irrespective of the number of events an ID has. Perfect, thank you!