Hi,
I am getting below error when I try to run attached code. There is some issue with data type.How can it be rectified in the case step
ERROR: Result of WHEN clause 3 is not the same data type as the preceding results.
data one;
infile datalines missover;
input id specifier $ ;
datalines;
01 class1
02 123
03 3456
04 aaaa
05 N/A
06
;
run;
proc sql;
create table two as select id,
case
when specifier is missing then "unspecified"
when specifier="aaaa" or specifier="N/A" then "12345"
when length(specifier)=3 then specifier=cats(0,specifier)
else specifier
end as specifier_new
from one;
quit;
There is a small issue in your query. You have mentioned specifier= after "then" in 3rd CASE-WHEN statement.
Here is the corrected query:
data one;
infile datalines missover;
input id specifier $ ;
datalines;
01 class1
02 123
03 3456
04 aaaa
05 N/A
06
;
run;
proc sql;
create table two as select id,
case
when specifier is missing then "unspecified"
when specifier="aaaa" or specifier="N/A" then "12345"
when length(specifier)=3 then cats(0,specifier)
else specifier
end as specifier_new
from one;
quit;
There is a small issue in your query. You have mentioned specifier= after "then" in 3rd CASE-WHEN statement.
Here is the corrected query:
data one;
infile datalines missover;
input id specifier $ ;
datalines;
01 class1
02 123
03 3456
04 aaaa
05 N/A
06
;
run;
proc sql;
create table two as select id,
case
when specifier is missing then "unspecified"
when specifier="aaaa" or specifier="N/A" then "12345"
when length(specifier)=3 then cats(0,specifier)
else specifier
end as specifier_new
from one;
quit;
From now on, please when there are problems in the log, show use the ENTIRE log for this PROC. Do not show us partial logs!
WHEN clause 3 is this:
when length(specifier)=3 then specifier=cats(0,specifier)
What is the value of the part specifier=cats(0,specifier)??? This is a Boolean comparison, does the variable on the left equal the value of the function on the right? So the value of this Boolean comparison is numeric and has values either 0 or 1 or missing. A numeric cannot be assigned by this WHEN clause and a character string is assigned by other WHEN clauses.
Probably this is what you want:
when length(specifier)=3 then cats(0,specifier)
because cats(0,specifier) is a character string, that works with other WHEN clauses also assign character strings.
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.