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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.