Case When Statement

[KC_16]

Hi,

I have written a case statement within my SQL code to create a new variable, however the original variable "DATE" is a CHAR and once my new variable has been created "new_date_var", it converts to a NUM and outputs 0 for all records, any idea why or how to resolve this please?

proc sql;
create table work.RAC_Test as
select
Date_var,
amount,
case
when Date_var = "MO" then Date_var = 'Monthly'
when Date_var = "QU" then Date_var = 'Quarterly'
when Date_var = "SM" then Date_var = 'Six Monthly'
when Date_var = "AN" then Date_var = 'Annually'
when Date_var = "UN" then Date_var = 'Unknown'
end as new_date_var; 
from RGTest.Testing;
quit;

[Kurt_Bremser]

You have an erroneous semicolon before the "from" option.

proc sql;
create table work.RAC_Test as
select
  Date_var,
  amount,
  case
    when Date_var = "MO" then Date_var = 'Monthly'
    when Date_var = "QU" then Date_var = 'Quarterly'
    when Date_var = "SM" then Date_var = 'Six Monthly'
    when Date_var = "AN" then Date_var = 'Annually'
    when Date_var = "UN" then Date_var = 'Unknown'
  end as new_date_var /* removed ; here */
from RGFrcst.FirstMthValue;
quit;

PS the log should give you a clue. 

[KC_16]

Thanks for noticing the extra semi colon which I have now removed.

I have re run the code but I am still getting the same problem. I have added an 'else' to the end of the case statement thinking that was causing the issue, but now I get this error message - 

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

proc sql;
create table work.RAC_Test as
select
  Date_var,
  amount,
  case
    when Date_var = "MO" then Date_var = 'Monthly'
    when Date_var = "QU" then Date_var = 'Quarterly'
    when Date_var = "SM" then Date_var = 'Six Monthly'
    when Date_var = "AN" then Date_var = 'Annually'
    when Date_var = "UN" then Date_var = 'Unknown'
  else 'Blank' end as new_date_var 
from RGTest.Testing;
quit;

[Reeza]

That's not how a SQL CASE statement works either...

proc sql;
create table work.RAC_Test as
select
  Date_var,
  amount,
  case
    when Date_var = "MO" then  'Monthly'
    when Date_var = "QU" then  'Quarterly'
    when Date_var = "SM" then  'Six Monthly'
    when Date_var = "AN" then  'Annually'
    when Date_var = "UN" then  'Unknown'
    else 'Blank' end as new_date_var 
from RGTest.Testing;
quit;

The PROC SQL documentation is here:

http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n0a85s0ijz65irn1h3jtariooea5.htm&docsetV...

Note the examples under each construct such as CASE. 

[RW9]

I can give you a clue:

select
  Date_var,

And

  case
    when Date_var = "MO" then Date_var = 'Monthly'
    when Date_var = "QU" then Date_var = 'Quarterly'
    when Date_var = "SM" then Date_var = 'Six Monthly'
    when Date_var = "AN" then Date_var = 'Annually'
    when Date_var = "UN" then Date_var = 'Unknown'
  else 'Blank' end as new_date_var 

You see you are selecting a variable called date_var from the dataset - I assume this is a date and hence numeric.  Then in your case statement you are trying to apply text to this variable. 

This should solve it:

  case
    when Date_var = "MO" then 'Monthly'
    when Date_var = "QU" then 'Quarterly'
    when Date_var = "SM" then 'Six Monthly'
    when Date_var = "AN" then 'Annually'
    when Date_var = "UN" then 'Unknown'
  else 'Blank' end as new_date_var 

[Kurt_Bremser · Accepted solution]

Oh, overlooked that. See correct code:

proc sql;
create table work.RAC_Test as
select
  Date_var,
  amount,
  case
    when Date_var = "MO" then 'Monthly'
    when Date_var = "QU" then 'Quarterly'
    when Date_var = "SM" then 'Six Monthly'
    when Date_var = "AN" then 'Annually'
    when Date_var = "UN" then 'Unknown'
    else 'Blank'
  end as new_date_var
from RGFrcst.FirstMthValue;
quit;

[KC_16]

Thanks all for your help.