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;
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;
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.
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;
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:
Note the examples under each construct such as CASE.
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
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;
Thanks all for your help.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.