BookmarkSubscribeRSS Feed
MILKYLOVE
Calcite | Level 5

Hello,

 

I have a table named 'role' with several columns. I want to change the value of one of its columns based on this scenario:

 

If CD_ROLE is empty, then put SOURCE in CD_ROLE and put NU_ROLE in NU_CLI

if CD_ROLE is not empty then do nothing

 

Formats:

CD_ROLE : $2.

NU_ROLE: $20.

NU_CLI: $20.

SOURCE: $4. 

 

What I tried: 

 

PROC SQL;
CREATE TABLE r2;
SELECT r.*,
CASE WHEN CD_ROLE = ' ' THEN CD_ROLE = r.SOURCE 
WHEN CD_ROLE = ' ' THEN NU_CLI = r.NU_ROLE
ELSE CD_ROLE 
END AS CD_ROLE length = 20
FROM role r;
quit;

I am getting error: 

Result of WHEN clause 3 is not the same data type as the preceding results.

 

Please help 😞 

4 REPLIES 4
PaigeMiller
Diamond | Level 26

@MILKYLOVE wrote:

I am getting error: 

Result of WHEN clause 3 is not the same data type as the preceding results.


This happens because somewhere, you are trying to assign a numeric variable to a character variable, or the other way around. So, what are the variable types (numeric or character) of the variables in use in this problem? 


From now on, when you get an error, show us the ENTIRE log for this PROC or DATA step. Do not show us the error message detached from the rest of the log for this PROC or DATA step.

--
Paige Miller
MILKYLOVE
Calcite | Level 5

All the variables are of 'character' data type. That's why I am confused as to why I am getting this error message. 

 

Maybe I have wrongly written the code as well?

PaigeMiller
Diamond | Level 26

Please show us the ENTIRE log for this PROC SQL. Copy the log as text, and paste it into the window that appears when you click on the </> icon. 

PaigeMiller_0-1663012019648.png

--
Paige Miller
ballardw
Super User

ONE case statement assigns ONE value to ONE variable.

So your code is apparently trying to violate that.

CASE WHEN CD_ROLE = ' ' THEN  r.SOURCE 
ELSE CD_ROLE 
END AS CD_ROLE length = 20

and a second case for the

CASE WHEN CD_ROLE = ' ' THEN  r.NU_ROLE
else ' ' /* guessing as you did not provide an ELSE rule for NU_CLI*/ END AS NU_CLI length = 20

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 387 views
  • 0 likes
  • 3 in conversation