topic Re: datatype issue in case step output of proc sql in SAS Programming

datatype issue in case step output of proc sql

Sathya3 — Fri, 19 May 2023 09:18:52 GMT

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;

Re: datatype issue in case step output of proc sql

MayurJadhav — Fri, 19 May 2023 09:43:52 GMT

There is a small issue in your query. You have mentioned specifier= after "then" in 3rd CASE-WHEN statement.

@Sathya3

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;

Re: datatype issue in case step output of proc sql

PaigeMiller — Fri, 19 May 2023 11:17:45 GMT

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.