BookmarkSubscribeRSS Feed
rebelde52
Fluorite | Level 6

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_Numberdatestatusrank
A122733M11
A122733P12
A122733P11
A122733P13
A122740P12
A122740P11
A122740P11
A122740P13

 

Want

PT_NumberdatestatusrankExclusion_Variable
A122733M111
A122733P121
A122733P11 
A122733P131
A122740P12 
A122740P11 
A122740P11 
A122740P13 
8 REPLIES 8
mkeintz
PROC Star

Your IF conditions has many issues.

 

  1. 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.

  2. 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')

  3. And you also have and status = 'P1', which can never be true if status is in one of the above comma-separated values.

  4. 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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
SASKiwi
PROC Star

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". 

rebelde52
Fluorite | Level 6
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.  
SASKiwi
PROC Star

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

rebelde52
Fluorite | Level 6
Well I want this apply to the whole dataset since I want this to apply to all Patients
rebelde52
Fluorite | Level 6

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_Numberdatestatusrank
A122733M11
A122733P12
A122733P11
A122733P13
A122740P12
A122740P11
A122740P11
A122740P13

 

Want

PT_NumberdatestatusrankExclusion_Variable
A122733M111
A122733P121
A122733P11 
A122733P131
A122740P12 
A122740P11 
A122740P11 
A122740P13 
Kurt_Bremser
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1694 views
  • 1 like
  • 4 in conversation