problem with case statement

[noetsi]

This error is so basic I am baffled. Note I changed the names in this example because the data is sensitive and I can't display it. 

proc sql;
Create table dora.counselor3
as select a.*
, case when 'first name'n= 'John' and 'last name'n ='Smith' then 'RIMS Staff ID If you use RIMS, y'n = '1111'
else 'RIMS Staff ID If you use RIMS, y'n end as correctid

from dora.counselor2 a;
quit;

'RIMS Staff ID If you use RIMS, y'n is a string field

I get ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Which makes no sense. The data I put in is a string value and so is the variable in question.

[Reeza · Accepted solution]

You're assigning a value to  'RIMS Staff ID If you use RIMS, y'n = '1111'

but end the CASE statement with:

end as correctid

I think you're mixing up IF/THEN notation and CASE notation. 

I think this is what you're looking for here:

case when 'first name'n= 'John' and 'last name'n ='Smith' then  '1111'
else 'RIMS Staff ID If you use RIMS, y'n 
end as correctid

---

@noetsi wrote:

This error is so basic I am baffled. Note I changed the names in this example because the data is sensitive and I can't display it. 

proc sql;
Create table dora.counselor3
as select a.*
, case when 'first name'n= 'John' and 'last name'n ='Smith' then 'RIMS Staff ID If you use RIMS, y'n = '1111'
else 'RIMS Staff ID If you use RIMS, y'n end as correctid

from dora.counselor2 a;
quit;

'RIMS Staff ID If you use RIMS, y'n is a string field

I get ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Which makes no sense. The data I put in is a string value and so is the variable in question.

---

---

[noetsi]

thank you. As I feared a very basic error. I rarely work with multiple fields in the case statement. Usually I just work with the one I am changing. In fact I fixed this, before I saw the replies, with an insert statement.

[ballardw]

Style comment: If your variable names are so sensitive they can't be shared then you might talk to someone about considering simpler variable names that are not sensitive and place the "sensitive" information into a LABEL for SAS variables. Then you can have much more coherent internal report text using the labels and don't try to create such ugly variables, even in dummy code, as 'RIMS Staff ID If you use RIMS, y'n. I choke just thinking about how many times I would screw up typing that sort of variable name. Plus SAS variable labels can be much longer than variable names, up to 256 characters.

I strongly suspect that your code should have thrown an error. You should include the complete log of code plus notes, warnings and errors for almost anything. Copy from the log, on the forum open a text box using the </> icon that appears above the message window and paste the text. You can edit the pasted text if you really have something that sensitive.

[Tom]

Is that third variable NUMERIC or CHARACTER?  Your current code it treating it as both.

You have this syntax.

case
  when A='John' and B='Smith' then (C = '1111')
  else C
end as D

So in the first place you use C you are comparing it to a string literal.  So you are treating it as a character variable. The result of the comparison will be a numeric value of either 0 or 1 depending on whether the value of C matches the string. So the new variable D should be numeric.  But in the ELSE clause you are taking the value of C to use as the value for the new numeric variable D.  So is C a character string or a number?

I suspect you meant to code this pattern instead.

case
  when A='John' and B='Smith' then '1111'
  else C
end as D

Now the variable C should be a character string to match the string literal result of the first WHEN clause.