BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KC_16
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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
Fluorite | Level 6

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Fluorite | Level 6

Thanks all for your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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