# Sas proc sql - null **URL:** \ **Category:** Factual Questions\ **Created:** [November 3, 2016, 12:25pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527 "2016-11-03T12:25:47Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![AllShookDown](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@AllShookDown](https://boards.straightdope.com/u/AllShookDown)\ **Post date:** [November 3, 2016, 12:25pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/1 "2016-11-03T12:25:47Z")
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. ---
**Author:** ![scudsucker](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/scudsucker/32/14101_2.png) [@scudsucker](https://boards.straightdope.com/u/scudsucker)\ **Post date:** [November 3, 2016, 1:52pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/2 "2016-11-03T13:52:20Z")
I’m not an Oracle nerd, but seems to me, ```auto 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 ---
**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\ **Post date:** [November 3, 2016, 2:39pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/3 "2016-11-03T14:39:42Z")
**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… ---
**Author:** ![Raza](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/raza/32/213_2.png) [@Raza](https://boards.straightdope.com/u/Raza)\ **Post date:** [November 3, 2016, 2:47pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/4 "2016-11-03T14:47:04Z")
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: ```auto 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. ---
**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\ **Post date:** [November 3, 2016, 2:53pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/5 "2016-11-03T14:53:51Z")
[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(). ---
**Author:** ![Raza](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/raza/32/213_2.png) [@Raza](https://boards.straightdope.com/u/Raza)\ **Post date:** [November 4, 2016, 1:40pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/6 "2016-11-04T13:40:55Z")
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. ---
**Author:** ![AllShookDown](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@AllShookDown](https://boards.straightdope.com/u/AllShookDown)\ **Post date:** [November 7, 2016, 1:21pm UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/7 "2016-11-07T13:21:30Z")
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. ```auto 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, ``` ---
**Author:** ![albino\_manatee](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/albino_manatee/32/2_2.png) [@albino\_manatee](https://boards.straightdope.com/u/albino_manatee)\ **Post date:** [November 8, 2016, 3:43am UTC](https://boards.straightdope.com/t/sas-proc-sql-null/770527/8 "2016-11-08T03:43:33Z")
seems like an sql-injection hack to me.