CASE WHEN Column1 = '.'

[Stepik]

Dear team,

Column 1 consists of numeric values and also missing ones assigned just with one dot '.'.

I would like to create a new column with the outcome depending if Column1 numeric or '.'.

Below is the simplified code using PROC SQL:

case
when Column1 = '.'
then 3
else 4 
end

The next error appears "ERROR: Expression using equals (=) has components that are of different data types.".

I also tried to use IFN function, IFN (Column1 = '.', 3, 4) It gives the same ERROR.

Can someone assist please?

Thanks

[Reeza · Accepted solution]

. is SAS notation for missing for numeric. So all values are numeric.

This should work:

case when missing(column1) then 3 else 4 end

[Tom]

So the error message is say that the comparison operator

Column1 = '.'

is trying to compare values of different TYPEs.  SAS only has two types.  Floating point numbers and fixed length character strings.   Since the right side is clearly a character constant then COLUMN1 must be a NUMERIC variable.  So you need to compare it to a numeric value.

Column1 = .

Which will test if column1 has normal missing value.

If you want to test if COLUMN1 has other missing values, such as .A or .Z, then you could use the MISSING() function instead of the comparison operator.

missing(Column1)

Note that the MISSING() function will also work if COLUMN1 is character.  SAS will treat a character value that only contains spaces as missing.

[Stepik]

Tom, what would be the solution if I would like to use IFN (IFC) function?

[PaigeMiller]

Same as has been discussed above, you use column1=. instead of column1='.'