BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sathya3
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
MayurJadhav
Quartz | Level 8

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;
Mayur Jadhav
BI Developer. Writer. Creative Educator.

SAS Blog → https://learnsascode.com
YouTube Channel: → https://www.youtube.com/@imayurj

View solution in original post

2 REPLIES 2
MayurJadhav
Quartz | Level 8

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;
Mayur Jadhav
BI Developer. Writer. Creative Educator.

SAS Blog → https://learnsascode.com
YouTube Channel: → https://www.youtube.com/@imayurj
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 327 views
  • 2 likes
  • 3 in conversation