topic Re: Case When Statement in SAS Programming

Case When Statement

KC_16 — Tue, 27 Feb 2018 16:48:35 GMT

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;

Re: Case When Statement

Kurt_Bremser — Tue, 27 Feb 2018 16:43:00 GMT

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.

Re: Case When Statement

KC_16 — Tue, 27 Feb 2018 16:49:17 GMT

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;

Re: Case When Statement

Reeza — Tue, 27 Feb 2018 16:52:48 GMT

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&docsetVersion=9.4&locale=en

Note the examples under each construct such as CASE.

Re: Case When Statement

RW9 — Tue, 27 Feb 2018 16:53:25 GMT

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

Re: Case When Statement

Kurt_Bremser — Tue, 27 Feb 2018 16:55:13 GMT

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;

Re: Case When Statement

KC_16 — Tue, 27 Feb 2018 16:59:12 GMT

Thanks all for your help.