topic Re: Filtering subject based on previous value/condition in SAS Programming

Filtering subject based on previous value/condition

Batta — Thu, 14 Oct 2021 18:44:04 GMT

Hi guys,
I hope you can help with this (please check "Data set" below):

I have subjects that can express different Conditions (Con1-Con4, as given). I can group/order the subjects (column Order) based on time points/dates (not shown), subjects express one of mentioned conditions at certain point of time/order number.

What I am interested here is to select only those subject that have Con4 when previous condition (for the same subject) was con3 - for this data set example I would need only need to filter Subj3 and listed conditions (last condition was Con4 and previous was Con3) while Subj1 does not satisfy that criteria (it has the last condition Con4 but previous one/time point was Con2).

Subject	Order	Condition
Subj1	1	Con1
Subj1	2	Con2
Subj1	3	Con2
Subj1	4	Con4
Subj2	1	Con2
Subj2	2	Con4
Subj3	1	Con2
Subj3	2	Con3
Subj3	3	Con4

I hope I didn't made it too messy in my explanation... 🙂

Thank you very much for your time.

Re: Filtering subject based on previous value/condition

HB — Thu, 14 Oct 2021 20:02:26 GMT

data have;
   input subject $5. order 2. condition $5.;
datalines;
Subj1 1 Con1
Subj1 2 Con2
Subj1 3 Con2
Subj1 4 Con4
Subj2 1 Con2
Subj2 2 Con4
Subj3 3 Con4
Subj3 1 Con2
Subj3 2 Con3
;
run;

proc sort data = have;
	by subject order;
run;

data want;
	set have;
	prev_cond = lag(condition);
	if prev_cond = "Con3" and condition = "Con4";
run;

Output is

subject	order	condition	prev_cond
Subj3	3	Con4	Con3

Does something like this move you in the right direction?

Edit: Because it is what I do, here is a possible Proc SQL solution

proc sql;
	select a.subject, a.order, a.condition, b.condition
	from have a left join have b
	on a.subject = b.subject and a.order -1 = b.order
	where a.condition = 'Con4' and b.condition = 'Con3';
quit;

Re: Filtering subject based on previous value/condition

Batta — Fri, 15 Oct 2021 17:37:36 GMT

Thank you very much!
This is very helpful.

Best
Batta