Sas proc sql - null

Factual Questions
1

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.

2

I’m not an Oracle nerd, but seems to me,




sum(case when REC_IND = 'L' and (CLD_IND IS NULL OR CLD_IND  IN (' ','2')) then CLM_CNT else 0 end) as CLMS_REPORTED

May do it

3

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

… and NVL(CLD_IND, ‘2’) IN (’’,‘2’) then…

4

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.

5

[QUOTE=Raza]

The result is “bad”, so the null isn’t interpreted the way it would be in the clause IS NULL.
[/QUOTE]

Null never equals anything, even another null. That’s why you use ‘IS NULL’ or NVL().

6

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.

7

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,
8

seems like an sql-injection hack to me.