Proc SQL - Where ?

[ybz12003]

Hello:

I have some codes from the past (see the end).   I would like to modify the following codes as my request below.

1.  Bloodculture = 1 and CultureDate ^=. ( Must have)

2.  When AdmitDate is not missing, CultureDate less than AdmitDate . (Option 1)

3.  When DischargeDate is not missing, CultureDate less than DischargeDate . (Option 2)

4.  When ERAdmitDate is not missing, CultureDate less than ERAdmitDate . (Option 3)

I have over 30 similar codes using PROC SQL to do the inquiry.   Therefore, I am ONLY looking for using 'PROC SQL WHERE' to do this task.   Could someone help me how to modify the codes below and meet the requirement.   Thank you.

proc sql;
	create table Want as
 	select site, id, culturedate, dischargedate, admitdate,ERadmitdate, bloodculture
 	from have
 	where bloodculture = 1 and culturedate ^= . or (culturedate lt admitdate-1 and admitdate ne .) or (culturedate gt dischargedate and dischargedate ne .) 
			or (culturedate lt EDadmitdate-1 and EDadmitdate ne .)
 	order by site, id;

[Astounding]

Here's the overall format for the WHERE clause.  You will need to supply the correct logic for each option.

where bloodculture = 1 and culturedate ne . and
   ( (logic for option 1) or
     (logic for option 2) or
     (logic for option 3)
   )

[Tom · Accepted solution]

Don't mix AND and OR without proper grouping or you won't get what you want.

Do you want this:

where bloodculture = 1
  and culturedate ^= . 
  AND ( (culturedate lt admitdate-1 and admitdate ne .)
     or (culturedate gt dischargedate and dischargedate ne .)
     or (culturedate lt EDadmitdate-1 and EDadmitdate ne .)
      )

Or this:

where ( bloodculture = 1
    and culturedate ^= . 
      )
  OR  ( (culturedate lt admitdate-1 and admitdate ne .)
     or (culturedate gt dischargedate and dischargedate ne .)
     or (culturedate lt EDadmitdate-1 and EDadmitdate ne .)
      )

[ybz12003]

Awesome!  Thanks much, Tom!

[PGStats]

Warning: Subsetting conditions involving OR clauses are handled inefficiently by SAS SQL.

Sometimes, such logical conditions can't be avoided. But sometimes they can. See above.

[PGStats]

proc sql;
create table Want as
select 
    site, 
    id, 
    cultureDate, 
    dischargeDate, 
    EDadmitDate, 
    ERadmitDate, 
    bloodCulture
from have
where 
    bloodCulture = 1 and 
    cultureDate is not missing and
    cultureDate < max(EDadmitDate, dischargeDate, ERadmitDate)
order by site, id;