data cleaning, deleting obsevations for visitdate within one year

[Kyra]

Hi,

I have a dataset looking like below.

MRN    Date_of_proc

1029   11/9/15

1029    12/9/15

1029    1/9/17

1029     2/9/18

1050     12/5/17

1080      1/7/16

1080      2/5/18

I want to delete the observations for which Date_of_proc is within one year.and i want it look like this

MRN    Date_of_proc

1050     12/5/17

1080      1/7/16

1080      2/5/18

Please help. Thank you in advance.

[novinosrin]

Can you clarify  within one year of what?

[Kyra]

Hi,

Like for one MRN,  if any of two Date _of_proc is less than a year apart i want to delete that MRN.

MRN 1029 has Date _of_proc which was less than a year apart so i want to delete 1029.

1080 has Date _of_proc more than a year apart so i want to keep this MRN.

Thanks

[ballardw]

---

@Kyra wrote:

Hi,

Like for one MRN,  if any of two Date _of_proc is less than a year apart i want to delete that MRN.

MRN 1029 has Date _of_proc which was less than a year apart so i want to delete 1029.

1080 has Date _of_proc more than a year apart so i want to keep this MRN.

Thanks

---

Next question: are your date values actual SAS date values with a SAS date format applied or are they character? If they are character the first step is to create an actual SAS date value so you can compare values.

Second: I think your definition of "more than a year apart" needs some expansion as when I look at the following I do not actually see a difference for exclusion/inclusion when compared with MRN=1080

1029    12/9/15
1029    1/9/17

[Kyra]

two of the dates for 1029 are 11/9/15 and 12/9/15 which is within one year so i want to delete this MRN.

when i run the proc contents below is what comes up for my date variable

SAS Output

Date_of_proc

Num

8

DATE9.

DATE9.

Date_of_proc

[ballardw]

---

@Kyra wrote:

two of the dates for 1029 are 11/9/15 and 12/9/15 which is within one year so i want to delete this MRN.

when i run the proc contents below is what comes up for my date variable

SAS Output

Date_of_proc

Num

8

DATE9.

DATE9.

Date_of_proc

---

So at least we have date value to work with.

So is your rule actually: if ANY two dates within the value of MRN are less than a year apart then delete the entire MRN.

Now is a "year" always 365 days or are you considering leap days in the interval determination?

[Kyra]

if ANY two dates within the value of MRN are less than a year apart then delete the entire MRN- Yes this is correct.

A year is always 365 for me.

[novinosrin · Accepted solution]

data have;
input MRN    Date_of_proc :mmddyy10.;
format Date_of_proc mmddyy10.;
cards;
1029   11/9/15
1029    12/9/15
1029    1/9/17
1029     2/9/18
1050     12/5/17
1080      1/7/16
1080      2/5/18
;
proc sort data=have;
by mrn Date_of_proc;
run;

data want;
_f=.;
do until(last.mrn);
set have;
by mrn;
_temp_=dif(date_of_proc);
if first.mrn then call missing(_temp_);
if .<_temp_<365 then _f=1;
end;
do until(last.mrn);
set have;
by mrn;
if not _f then output;
end;
drop _:;
run;

[Kyra]

Thank you very much . It worked.

[FreelanceReinh]

@novinosrin: Good. I think you can save three lines with

if .<dif(date_of_proc)<365 & ~first.mrn then _f=1;

[Kyra]

ok. will look into it.

[novinosrin]

@Kyra  Improving upon @FreelanceReinh sir's neat suggestion 🙂

data want;
do until(last.mrn);
set have;
by mrn;
if not first.mrn and .<dif(Date_of_proc)<365 then _f=1;
end;
do until(last.mrn);
set have;
by mrn;
if not _f then output;
end;
drop _:;
run;

[Kyra]

Thank you very much for these codes. But the previous codes were also correct, Right?

[novinosrin]

Yes indeed it is correct. But we learn from guru 's like  @FreelanceReinh , @ballardw to further speed up learning and optimise processes.

I am privileged to get such selfless mentors

And hey, Don't worry or dwell too much. Perfectionists are a blessing, if and when you become a regular here, you would get those too. 🙂  I get them in many threads

PS

Idea is to get those champs interested in getting to see what we have done. That in itself is a privilege. 

[Kyra]

Thank you very much. I have just started to work on SAS so wanted to make sure i used correct codes. This forum is an asset to beginners like me.