DATA Step, Macro, Functions and more

Case When Statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Case When Statement

[ Edited ]

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;


Accepted Solutions
Solution
‎02-27-2018 11:58 AM
Super User
Posts: 10,623

Re: Case When Statement

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,623

Re: Case When Statement

[ Edited ]

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. 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Case When Statement

[ Edited ]
Posted in reply to KurtBremser

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;
Super User
Posts: 24,027

Re: Case When Statement

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. 

Super User
Super User
Posts: 9,866

Re: Case When Statement

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 
Solution
‎02-27-2018 11:58 AM
Super User
Posts: 10,623

Re: Case When Statement

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Case When Statement

Posted in reply to KurtBremser

Thanks all for your help.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 175 views
  • 0 likes
  • 4 in conversation