topic Re: data cleaning, deleting obsevations for visitdate within one year in SAS Procedures

data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 16:55:09 GMT

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.

Re: data cleaning, deleting obsevations for visitdate within one year

novinosrin — Fri, 09 Nov 2018 16:57:32 GMT

Can you clarify within one year of what?

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 17:24:34 GMT

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

Re: data cleaning, deleting obsevations for visitdate within one year

ballardw — Fri, 09 Nov 2018 17:33:02 GMT

@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

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 17:49:49 GMT

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

DATE9.

Date_of_proc

Re: data cleaning, deleting obsevations for visitdate within one year

ballardw — Fri, 09 Nov 2018 18:02:53 GMT

@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?

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 18:11:49 GMT

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.

Re: data cleaning, deleting obsevations for visitdate within one year

novinosrin — Fri, 09 Nov 2018 18:32:12 GMT

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;

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 18:42:23 GMT

Thank you very much . It worked.

Re: data cleaning, deleting obsevations for visitdate within one year

FreelanceReinh — Fri, 09 Nov 2018 18:42:56 GMT

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

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

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 18:47:10 GMT

ok. will look into it.

Re: data cleaning, deleting obsevations for visitdate within one year

novinosrin — Fri, 09 Nov 2018 19:08:20 GMT

@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;

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 21:52:18 GMT

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

Re: data cleaning, deleting obsevations for visitdate within one year

novinosrin — Fri, 09 Nov 2018 22:02:04 GMT

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

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

Re: data cleaning, deleting obsevations for visitdate within one year

Kyra — Fri, 09 Nov 2018 22:01:52 GMT

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.