topic Re: Setting a column value with an existing value in case expressions in SAS Programming

Setting a column value with an existing value in case expressions

MILKYLOVE — Wed, 30 Aug 2023 10:29:11 GMT

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 😞

Re: Setting a column value with an existing value in case expressions

PaigeMiller — Wed, 30 Aug 2023 11:38:00 GMT

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

Re: Setting a column value with an existing value in case expressions

MILKYLOVE — Wed, 30 Aug 2023 11:51:49 GMT

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?

Re: Setting a column value with an existing value in case expressions

PaigeMiller — Wed, 30 Aug 2023 11:59:31 GMT

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.

Re: Setting a column value with an existing value in case expressions

ballardw — Wed, 30 Aug 2023 15:11:20 GMT

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