DATA Step, Macro, Functions and more

How do I remove redundant datelines within a predefined period?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How do I remove redundant datelines within a predefined period?

Hi

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


Accepted Solutions
Solution
‎01-23-2018 05:36 AM
Super User
Posts: 9,866

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎01-23-2018 05:36 AM
Super User
Posts: 9,866

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 5

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

Posted in reply to KurtBremser

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

Contributor
Posts: 44

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

[ Edited ]

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

Occasional Contributor
Posts: 5

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

Posted in reply to jmhorstman

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!

Contributor
Posts: 44

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

[ Edited ]

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.  Smiley Happy

 

Josh

Occasional Contributor
Posts: 5

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

Posted in reply to jmhorstman

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.

Contributor
Posts: 44

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

Nope you used it correctly. It was my error. See above for the corrected version. Thanks!
Occasional Contributor
Posts: 5

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

Posted in reply to jmhorstman

Ok - that makes sense:-) Thanks again!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 96 views
  • 2 likes
  • 3 in conversation