Proc SQL Case When not working.

[ChickenLittle]

Hi,

I am merely trying to convert a field containing "0" or "1" into "F" or "P". The field was formatted as a character field and when I use the Case When statement, it is not working.

What I have (MyDataSet is formatted as a character field):

Obs    Field1

1          0

2          1

3          0

What I want:

Obs    Field2

1          F

2          P

3          F

My Code:

proc sql;
create table Table_Name as select
case when Field1 = '1' then 'P'
         when Field1 = '0' then 'F' else 'N/A'
         end as Field2
from MyDataSet;
quit;
run;

Result I am getting from my code:

Obs    Field2

1          N/A

2          N/A

3          N/A

[Tom · Accepted solution]

So you have discovered that the actual values in FIELD1 are NOT the single digit strings '1' or '0'.

Perhaps FIELD1 is a number.

Perhaps FIELD1 has a format attached to it that is making the values print as '1' or '0'.

Perhaps FIELD1 values are actually a lowercase l and an uppercase O.

Perhaps FIELD1 values have leading spaces in them.

Perhaps FIELD1 values have other invisible characters, like tabs, in them.