topic CASE WHEN Column1 = '.' in SAS Enterprise Guide

CASE WHEN Column1 = '.'

Stepik — Fri, 16 Jun 2023 15:21:24 GMT

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

Re: CASE WHEN Column1 = '.'

Reeza — Fri, 16 Jun 2023 15:34:50 GMT

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

This should work:

case when missing(column1) then 3 else 4 end

Re: CASE WHEN Column1 = '.'

Tom — Fri, 16 Jun 2023 16:09:28 GMT

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.

Re: CASE WHEN Column1 = '.'

Stepik — Mon, 19 Jun 2023 08:42:03 GMT

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

Re: CASE WHEN Column1 = '.'

PaigeMiller — Mon, 19 Jun 2023 09:44:52 GMT

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