topic Re: How do I remove redundant datelines within a predefined period? in SAS Programming

How do I remove redundant datelines within a predefined period?

TineKopp — Mon, 22 Jan 2018 13:56:48 GMT

I have retrieved data on abortions (date of abortions=d_inddto)) from a hospital discharge registry. Sometimes there are several abortions (dates) per person within a short period of time (for example 4 dates in 10 days), which I know is not right. On the other hand, one person can have multiple abortions - but there should be at least 12 weeks between each abortion. So, I want to make a rule that says that in a period of 12 weeks or 84 days, only 1 abortion (that is, only on date) should remain in my dataset per person (PNR). And it should only be the first date. How do I do that? My data looks like this:

PNR d_inddto

1 13/11/2013

1 14/11/2013

1 20/11/2013

1 22/11/2013

2 24/05/2015

3 01/09/2006

Re: How do I remove redundant datelines within a predefined period?

Kurt_Bremser — Mon, 22 Jan 2018 14:04:24 GMT

data want;
set have;
by PNR;
retain last_date;
if first.PNR
then last_date = d_inddto;
else if intck('week',last_date,d_inddto) le 12
then delete;
else last_date = d_inddto;
run;

Untested. For tested code, supply example data in a data step.

Re: How do I remove redundant datelines within a predefined period?

jmhorstman — Mon, 22 Jan 2018 14:52:23 GMT

How about something like this?

data want;
	set have;
	by pnr;
	_prevdate = lag(d_inddto);
	if first.pnr or (d_inddto - _prevdate) >= 84;
	drop _prevdate;
run;

This is just based on 84 days, without regard to week boundaries. It assumes the input dataset is already sorted by ascending values of pnr and d_inddto.

Josh

EDIT: syntax correction to code above

Re: How do I remove redundant datelines within a predefined period?

TineKopp — Mon, 22 Jan 2018 14:24:03 GMT

Thank you very much. It seems to work perfectly:-)

Re: How do I remove redundant datelines within a predefined period?

TineKopp — Mon, 22 Jan 2018 14:29:37 GMT

Thank you for your answer! Unfortunately, it removes all datelines except for the first one. Or maybe I haven't used it correctly..... Nevertheless, I got an answer by KurtBremser that seems to work better on my data. Again, thank you!

Re: How do I remove redundant datelines within a predefined period?

jmhorstman — Mon, 22 Jan 2018 14:53:22 GMT

My mistake - I made a slight in error in the code. I've corrected it above. My apologies - I was in too much of a hurry to be the first responder and earn precious badges. 🙂

Josh

Re: How do I remove redundant datelines within a predefined period?

TineKopp — Mon, 22 Jan 2018 14:55:15 GMT

It is correct that in the very small dataset, I would expect only 3 records. However, if the dataset looked like this:

PNR d_inddto

1 13/11/2013

1 14/11/2013

1 20/11/2013

1 22/11/2013

1 03/05/2017

2 24/05/2015

3 01/09/2006

there should be 4 records left. I haven't tried it myself since I'm just trying to help a colleague. She said it didn't work on her data. But as I wrote, we may have used it incorrectly.

Re: How do I remove redundant datelines within a predefined period?

jmhorstman — Mon, 22 Jan 2018 14:58:04 GMT

Nope you used it correctly. It was my error. See above for the corrected version. Thanks!

Re: How do I remove redundant datelines within a predefined period?

TineKopp — Mon, 22 Jan 2018 15:01:59 GMT

Ok - that makes sense:-) Thanks again!