Good morning. I’m a SAS data step person, not a PROC SQL person but I have to deal with this this morning because my co-worker is off today and no one else is in yet and I have a bazillion jobs to re-run because of this.
This is straight PROC SQL on an Oracle table, not pass through SQL. (which I am not conversant in, at all). I was told to replace a straightforward line of code reading in a column from a table, with this:
sum(case when REC_IND = ‘L’ and CLD_IND in (null,’ ',‘2’) then CLM_CNT else 0 end) as CLMS_REPORTED
The problem is that SAS doesn’t like null. I tried NULL (just in case it’s case sensitive), IS_NULL, ISNULL, IS_MISSING, ISMISSING, ‘’ AND ‘.’
At least ‘’ and ‘.’ don’t error out but they also don’t give me the expected result. Help! I’m running on 4 hours of sleep because of the Cubs and I have 30 jobs to re-run because of this stupid data that was passed to our table.
scud’s version looks like it may work. I don’t think a bare ‘null’ will match anything, you need to use the ‘IS NULL’ construct.
If that doesn’t work you could try wrapping CLD_IND in a NVL()
I know the question was about Oracle, but out of curiosity I tried it in SQL Server (I’ve never found a reason to have “null” in an IN clause).
It doesn’t error, but it doesn’t work, either:
declare @x int
set @x = null
if @x in (null, 2, 4)
print 'Good'
else
print 'bad'
The result is “bad”, so the null isn’t interpreted the way it would be in the clause IS NULL.
True, in theory, and ideally. But MS SQL Server has an option where if ANSI_NULLS is OFF a comparison to NULL can yield true/false. It’s not a good practice, obviously, but possible.
Wow, I was so frantic and busy I forgot I even posted that until yesterday afternoon. The problem was that my SQL writing co-worker had other stuff being summarized in the SQL he wrote and tested, which I was doing afterwards in a PROC SUMMARY (yes, I’m so old I don’t even use PROC MEANS) and that was causing my attempts to write this line of code to fail. This is what my other co-worker got to work.
case when (REC_IND = 'L' and CLD_IND in (' ','2')) OR (REC_IND = 'L' and CLD_IND IS missing) then CLM_CNT else 0 end as G_RPT,