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.
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.
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.