topic Re: How to delete observations based on dates in a by group in SAS Programming

How to delete observations based on dates in a by group

Joachim133 — Tue, 03 Nov 2020 09:40:42 GMT

Hello.

I have a dataset of patients having multiple hospital visits. Patients are included in my study, when they have a specific diagnose. I need to keep all visits and diagnoses prior to the inclusion date, and deleting all visits after the inclusion date.

Example of dataset (not real data). Inclusion when a patient have diagnose code '200':

Patient_ID	visit_ID	Date	Diagnose
1	1	01.01.20	111	Keep observation
1	2	02.01.20	154	keep observation
1	3	03.01.20	200	keep observation
1	4	04.01.20	085	delete observation
2	5	01.06.20	119	keep observation
2	6	02.06.20	543	keep observation
2	7	03.06.20	200	keep observation
3	8	01.09.19	746	keep observation
3	9	02.09.19	549	keep observation
3	10	03.09.19	200	keep observation
3	11	04.09.19	432	delete observation
3	12	05.09.19	543	delete observation

How can this be done?

I use SAS 9.4.

Thanks!

Re: How to delete observations based on dates in a by group

Kurt_Bremser — Tue, 03 Nov 2020 09:51:51 GMT

Retain a flag variable:

data have;
input Patient_ID  visit_ID  Date :ddmmyy8. Diagnose :$3.;
format date yymmdd10.;
datalines;
1 1 01.01.20  111
1 2 02.01.20  154
1 3 03.01.20  200
1 4 04.01.20  085
2 5 01.06.20  119
2 6 02.06.20  543
2 7 03.06.20  200
3 8 01.09.19  746
3 9 02.09.19  549
3 10  03.09.19  200
3 11  04.09.19  432
3 12  05.09.19  543
;

data want;
set have;
by patient_id;
retain flag;
if first.patient_id then flag = 1;
if flag;
if diagnose = "200" then flag = 0;
drop flag;
run;

Re: How to delete observations based on dates in a by group

ldamar — Tue, 03 Nov 2020 10:00:36 GMT

Hi,
This can be done easy using proc sql,
/* ------*/
proc sql;
create view FILTER as /* Creating a view you keep the patient_dataset intact */
select * from <patient_dataset>
where Diagnose = <diagnose Id> /* Filter for diagnose, use semicolons if the variable Diagnose is text */
and Date <= '<diagnose date literal>'d /* Filter for date */
;
quit;
/* ------*/

Regards

Re: How to delete observations based on dates in a by group

Joachim133 — Tue, 03 Nov 2020 10:15:01 GMT

Thank you!
However, I forgot to mention, that the including diagnose '200' may come several times per patient_ID. The cutoff should be the first time, the patient is admitted with the diagnose '200'. Does the code also work like that?

Re: How to delete observations based on dates in a by group

hhinohar — Tue, 03 Nov 2020 10:25:16 GMT

data have;
	infile datalines dlm="09"x;
	input Patient_ID visit_ID Date:mmddyy10. Diagnose;
	format date mmddyys10.;
	datalines;
1	1	01.01.20	111	Keep observation
1	2	02.01.20	154	keep observation
1	3	03.01.20	200	keep observation
1	4	04.01.20	085	delete observation
2	5	01.06.20	119	keep observation
2	6	02.06.20	543	keep observation
2	7	03.06.20	200	keep observation
3	8	01.09.19	746	keep observation
3	9	02.09.19	549	keep observation
3	10	03.09.19	200	keep observation
3	11	04.09.19	432	delete observation
3	12	05.09.19	543	delete observation
;
run;

data want;
	do until(last.patient_id or diagnose=200);
		set have;
		by patient_id;
		if first.patient_id then _N_=0;
		if _N_=0 then output;
	end;
run;

Re: How to delete observations based on dates in a by group

Kurt_Bremser — Tue, 03 Nov 2020 10:34:12 GMT

@Joachim133 wrote:

Thank you!
However, I forgot to mention, that the including diagnose '200' may come several times per patient_ID. The cutoff should be the first time, the patient is admitted with the diagnose '200'. Does the code also work like that?

Yes. Once the flag has been set to zero, it will stay that way until the next patient_id comes up.

Re: How to delete observations based on dates in a by group

Joachim133 — Tue, 03 Nov 2020 10:35:31 GMT

This works, thanks!

Re: How to delete observations based on dates in a by group

novinosrin — Tue, 03 Nov 2020 10:52:45 GMT



data have;
input Patient_ID  visit_ID  Date :ddmmyy8. Diagnose :$3.;
format date yymmdd10.;
datalines;
1 1 01.01.20  111
1 2 02.01.20  154
1 3 03.01.20  200
1 4 04.01.20  085
2 5 01.06.20  119
2 6 02.06.20  543
2 7 03.06.20  200
3 8 01.09.19  746
3 9 02.09.19  549
3 10  03.09.19  200
3 11  04.09.19  432
3 12  05.09.19  543
;

data want;
 do _n_=0 by 0 until(last.patient_id);
  set have;
  by patient_id;
  if not _n_ then output;
  if Diagnose='200' then _n_=constant('e');
 end;
run;

Hi @Joachim133 @Kurt_Bremser 's solution flag cut is elegance personified, i wanted to have some fun

Re: How to delete observations based on dates in a by group

novinosrin — Tue, 03 Nov 2020 11:04:25 GMT

data have;
input Patient_ID  visit_ID  Date :ddmmyy8. Diagnose :$3.;
format date yymmdd10.;
datalines;
1 1 01.01.20  111
1 2 02.01.20  154
1 3 03.01.20  200
1 4 04.01.20  085
2 5 01.06.20  119
2 6 02.06.20  543
2 7 03.06.20  200
3 8 01.09.19  746
3 9 02.09.19  549
3 10  03.09.19  200
3 11  04.09.19  432
3 12  05.09.19  543
;
Proc sql;
 create table want as
 select *
 from have
 group by patient_id
 having  date<=min(ifn(diagnose='200',date,.)) 
 order by patient_id,visit_id;
quit;

Re: How to delete observations based on dates in a by group

Joachim133 — Wed, 04 Nov 2020 12:20:58 GMT

Hey Kurt.

That sounds great, thank you very much.
I didnt see your response, before I flagged hhinhar's response as solution, but I know yours work just as well.
regards