PROC SQL Numeric value 'NULL' is not recognized

Question

I am trying to use case when statement to compare 2 columns with number and 'Null' value, the Proc SQL will return error as Numeric value 'NULL' is not recognized.

How to fix my code? Data type for Column A and B is VARCHAR(10).

Table alpha has 2 columns as 
column A include 1,2,3,Null,4
column B include 3,2,1,4,Null

SELECT  case when  A = B
        then 'YES' 
        else 'NO' end 
FROM alpha

I tried to use coalesce(B,2) to assign a number for Null value, but returned same error

error as Numeric value 'NULL' is not recognized.

However, I can use following code to find Null Row in Column a or b

select * from alpha 
where b = 'NULL'

OK I don't know it but I think you should look into how to cast those NULL values into numerics, or filter them out before they get to the = function. — Bret Weinraub, Aug 22 '22 at 21:35
It looks like you did not put NULL value into the table but "NULL" as a string. Can you verify this being the case? — xycf7, Aug 25 '22 at 20:28

score 0 · Answer 1 · answered Aug 31 '22 at 05:24

0

Check the table to make sure that the 'Null' value is actually NULL. Then use the 'IS NULL' statement to check b.

select * from alpha 
where b is null

answered Aug 31 '22 at 05:24

Vinnywoo

94
4

PROC SQL Numeric value 'NULL' is not recognized

1 Answers1