topic Re: Select rows based on the predefined amount of time since the last eligible data by group in SAS Programming

Select rows based on the predefined amount of time since the last eligible data by group

Cruise — Thu, 28 Feb 2019 21:54:17 GMT

Hi Folks,

I'm trying to create a data of patients who fulfills the following criteria:

Keep	IF: First observation of the patient
Delete	IF: Less than 12 mo since the last eligible data by patients

In other words:

The observation needs to be compared to the last eligible data. For instance, for the second subject (PAT_ID=2), third row is eligible because time passed since the last eligible observation is longer than 12 mo. The last eligible observation is the first row. Because second row was deleted due to its ineligibility.

DATA HAVE; 
INPUT PAT_ID DATE_VISITS DIFF_VISITS;
CARDS;
1	15391	.
1	15573	5.98
1	15945	12.22
2	15406	.
2	15586	5.92
2	15798	6.96
3	15775	.
3	16139	11.96
3	16321	5.98
4	15439	.
4	15621	5.97
4	16349	23.92
5	15438	.
5	15655	7.13
5	16019	11.96
6	15349	.
6	15558	6.87
6	15768	6.9
;

If you wonder why do this?

The biological marker concerned here is not highly dynamic over time. Therefore, you would not want to make a premature decision based on the observations captured over less than 12 months in between at least.

Re: Select rows based on the predefined amount of time since the last eligible data by group

novinosrin — Thu, 28 Feb 2019 22:28:42 GMT


DATA HAVE; 
INPUT PAT_ID DATE_VISITS DIFF_VISITS;
format DATE_VISITS date9.;
CARDS;
1	15391	.
1	15573	5.98
1	15945	12.22
2	15406	.
2	15586	5.92
2	15798	6.96
3	15775	.
3	16139	11.96
3	16321	5.98
4	15439	.
4	15621	5.97
4	16349	23.92
5	15438	.
5	15655	7.13
5	16019	11.96
6	15349	.
6	15558	6.87
6	15768	6.9
;

data want;
set have;
by PAT_ID;
retain d;
if first.pat_id then do;output; d=DATE_VISITS;end;
else do;
	dif=intck('month',d,DATE_VISITS);
	if dif>=12 then do;output;d=DATE_VISITS;end;
end;
run;

Re: Select rows based on the predefined amount of time since the last eligible data by group

novinosrin — Thu, 28 Feb 2019 22:32:18 GMT

@Cruise

Add the drop statement

drop d dif

to the previous

Re: Select rows based on the predefined amount of time since the last eligible data by group

Cruise — Fri, 01 Mar 2019 11:59:22 GMT

This is great. Thanks Novinosrin.

Re: Select rows based on the predefined amount of time since the last eligible data by group

novinosrin — Fri, 01 Mar 2019 13:56:25 GMT

Pleasure is mine:)