topic Re: How do I delete records with a date within 30 days of the previous record, recursively? in SAS Programming

How do I delete records with a date within 30 days of the previous record, recursively?

Fable — Sun, 18 Jun 2023 16:37:37 GMT

Say I have a dataset of laboratory records for a set of patients (ordered by ID and date from earliest to latest) with this format, I want to remove all the red colored labs and keep all the black colored ones.

Basically, I want to only keep labs for a specific patient (labs from different patients should not be compared against each other) that are done >30 days from the previous lab, while not counting the removed labs when evaluating the next lab (i.e. if a lab is marked in red I want to ignore it when evaluating if the next lab is within 30 days and should thus be removed).

You can see for patient 1111, their 3rd lab is within 30 days of their 2nd lab, but because the 2nd lab is already marked for removal the 2nd lab does not count and thus the 3rd lab should be kept.

Does any one have any advice or suggestions for how this could be accomplished in SAS?

Patient_ID	Lab_Date
1111	Jan 1 2023
1111	Jan 15 2023
1111	Feb 3 2023
1111	Feb 16 2023
2222	Jan 2 2023
2222	Jan 20 2023
2222	Feb 8 2023
2222	Feb 10 2023
2222	Feb 12 2023
3333	Jan 15 2023
3333	Feb 5 2023
3333	Feb 18 2023

Re: How do I delete records with a date within 30 days of the previous record, recursively?

FreelanceReinh — Sun, 18 Jun 2023 18:23:59 GMT

Hello @Fable and welcome to the SAS Support Communities!

Try this:

data have;
input Patient_ID Lab_Date anydtdte11.;
format Lab_Date yymmdd10.;
cards;
1111 Jan 1 2023
1111 Jan 15 2023
1111 Feb 3 2023
1111 Feb 16 2023
2222 Jan 2 2023
2222 Jan 20 2023
2222 Feb 8 2023
2222 Feb 10 2023
2222 Feb 12 2023
3333 Jan 15 2023
3333 Feb 5 2023
3333 Feb 18 2023
;

data want(drop=pivot_date);
set have;
by Patient_ID;
if first.Patient_ID then pivot_date=Lab_Date;
else if Lab_Date-pivot_date<=30 then delete;
else pivot_date=Lab_Date;
retain pivot_date;
run;

Re: How do I delete records with a date within 30 days of the previous record, recursively?

mkeintz — Mon, 19 Jun 2023 01:04:16 GMT

This is a good example for using a "subsetting if" and the lag function:

data have;
  input Patient_ID Lab_Date anydtdte11.;
  format Lab_Date yymmdd10.;
cards;
1111 Jan 1 2023
1111 Jan 15 2023
1111 Feb 3 2023
1111 Feb 16 2023
2222 Jan 2 2023
2222 Jan 20 2023
2222 Feb 8 2023
2222 Feb 10 2023
2222 Feb 12 2023
3333 Jan 15 2023
3333 Feb 5 2023
3333 Feb 18 2023
;

data want (drop=_:);
  set have;
  retain _refdate '01jan1900'd;
  if patient_id^=lag(patient_id) or lab_date-_refdate >30;
  _refdate=lab_date;
run;

It doesn't matter what value you use as the intial value of _refdate, because it will be immediately overwritten with the value of lab_date in the first observation. In fact, any non-missing number could be the initial value - I just decided to use a date-literal to remind the reader that it is being managed as a date variable.

Re: How do I delete records with a date within 30 days of the previous record, recursively?

whymath — Mon, 19 Jun 2023 04:27:07 GMT

Very creative solution!