topic Re: Proc SQL - Where ? in SAS Programming

Proc SQL - Where ?

ybz12003 — Mon, 25 Mar 2019 18:14:52 GMT

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;

Re: Proc SQL - Where ?

Astounding — Mon, 25 Mar 2019 18:23:00 GMT

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)
   )

Re: Proc SQL - Where ?

Tom — Mon, 25 Mar 2019 18:40:20 GMT

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

Re: Proc SQL - Where ?

PGStats — Mon, 25 Mar 2019 18:47:34 GMT

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;

Re: Proc SQL - Where ?

ybz12003 — Mon, 25 Mar 2019 19:00:12 GMT

Awesome! Thanks much, Tom!

Re: Proc SQL - Where ?

PGStats — Mon, 25 Mar 2019 21:59:04 GMT

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.