topic IF Then statement to create an exclusion variable by comparing dates. in SAS Programming

Creating an exclusion criteria (variable) using if then statement.

rebelde52 — Mon, 11 Sep 2023 04:49:45 GMT

Hello

I want to create an exclusion criteria/variable for a large dataset that includes the variables PT_Number, date, status, and rank.

I want to create an exclusion criteria so that I can later use a "Where" statement. I basically want this exclusion "

If the dates where rank does not equal 1 and status does not equal P1 are less than the dates with the rank that equal 1 and status that equal P1 then the Exclusion_Variable will equal 1.

*Note: there are other entries for rank such as (2,3,4) and for status such as (C1 CI, EA, etc). below is what I thought my code should look like and a table that I have and that I want. I also did test my code below and did a freq check and did not get any '1' in my exclusion variable frequency.

Am I missing something?

data Want;
set Have;
if date and rank ne 1 and status in ('C1,CI,EA,EB,F1,F2,F3,M1,M2,M4,M5,M7,M8,M9,MB,MH,MP,MS,N9,P1,P3,S4,VL,c1') < date and rank='1' and status='P1' then exclusion_variable='1';
run;

Have

PT_Number	date	status	rank
A1	22733	M1	1
A1	22733	P1	2
A1	22733	P1	1
A1	22733	P1	3
A1	22740	P1	2
A1	22740	P1	1
A1	22740	P1	1
A1	22740	P1	3

Want

PT_Number	date	status	rank	Exclusion_Variable
A1	22733	M1	1	1
A1	22733	P1	2	1
A1	22733	P1	1
A1	22733	P1	3	1
A1	22740	P1	2
A1	22740	P1	1
A1	22740	P1	1
A1	22740	P1	3

Re: Creating an exclusion criteria (variable) using if then statement.

mkeintz — Mon, 11 Sep 2023 12:51:59 GMT

Your IF conditions has many issues.

you are comparing rank to a numeric value ( rank ne 1 ) and to a character value ( rank='1'), so
1. You should get a note on the log about that inconsistency.
2. The "1" will be converted to a 1, so you are asking for rank ne 1 ... and rank=1.
Your list of comma-separated values in status in ('C1,CI,EA,......,VL,c1') is actually treated as a single very long text string. You need to surround each value with quotes, as in status in ('C1','CI','EA',.....'VL','c1')
And you also have and status = 'P1', which can never be true if status is in one of the above comma-separated values.
And what are the "IF DATE" and the "< DATE" expressions supposed to be specifying?

I'd suggest starting out with a simple single condition if test. Once that works as expected add other conditions step by step.

Re: Creating an exclusion criteria (variable) using if then statement.

SASKiwi — Mon, 11 Sep 2023 04:38:33 GMT

Please write your exclusion logic in plain English as this makes no sense at all:

If date and rank not = 1 and status not = P1 < date and rank=1 and status=P1 then Exclusion_Variable='1'.

"If date" , for example, is like saying "if date is not missing".

Re: Creating an exclusion criteria (variable) using if then statement.

rebelde52 — Mon, 11 Sep 2023 04:53:46 GMT

is this alittle better?

If the dates where rank does not equal 1 and status does not equal P1 are less than the dates with the rank that equal 1 and status that equal P1 then the Exclusion_Variable will equal 1.

Re: Creating an exclusion criteria (variable) using if then statement.

SASKiwi — Mon, 11 Sep 2023 05:08:06 GMT

Where does PT_Number come into it? Are you applying this logic by PT_Number?

Re: Creating an exclusion criteria (variable) using if then statement.

rebelde52 — Mon, 11 Sep 2023 05:13:45 GMT

Well I want this apply to the whole dataset since I want this to apply to all Patients

IF Then statement to create an exclusion variable by comparing dates.

rebelde52 — Mon, 11 Sep 2023 06:48:27 GMT

I am having so much trouble with this if then statement with multiple conditions.

I'm hoping someone can help me as I try to explain this in plain english and turn this into SAS code.

If the dates where Status is P1 and Rank is 1 is greater than the dates where any other combination of status and rank (not P1 and 1 respectively) Then that will equal 1 in the exclusion variable.

Have

PT_Number	date	status	rank
A1	22733	M1	1
A1	22733	P1	2
A1	22733	P1	1
A1	22733	P1	3
A1	22740	P1	2
A1	22740	P1	1
A1	22740	P1	1
A1	22740	P1	3

Want

PT_Number	date	status	rank	Exclusion_Variable
A1	22733	M1	1	1
A1	22733	P1	2	1
A1	22733	P1	1
A1	22733	P1	3	1
A1	22740	P1	2
A1	22740	P1	1
A1	22740	P1	1
A1	22740	P1	3

Re: IF Then statement to create an exclusion variable by comparing dates.

Kurt_Bremser — Mon, 11 Sep 2023 07:22:00 GMT

Please do not start a new thread for the same question, keep everything in one place. I merged this backin here.

Re: IF Then statement to create an exclusion variable by comparing dates.

Kurt_Bremser — Mon, 11 Sep 2023 07:28:46 GMT

Double DO loop:

data want;
_date = 99999;
do until (last.pt_number);
  set have;
  by pt_number;
  if status ne "P1" or rank ne 1 then _date = min(_date,date);
end;
do until (last.pt_number);
  set have;
  by pt_number;
  if status = "P1" and rank = 1 and date gt _date then exclusion_variable = 1;
  output;
end;
drop _date;
run;

This applies your rule, but it won't create your expected output, as 22733 is not greater than any other date.