Case When Error

[VarunD]

Hello,

I am hoping to replace the missing values in Description Variable with 'Unspecified'

Please help in identifying the cause of following error:

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Proc SQL;
Create Table Current2 AS
Select *,
Case when Description eq ' ' then Description eq 'Unspecified'
else Description
end as Description
FROM Current1 ;
QUIT;

P.S.Description variable is Character Type.

[Jagadishkatam]

please try

Proc SQL;
Create Table Current2 AS
Select *,
Case when Description eq ' ' then  'Unspecified'
else Description
end as Description
FROM Current1 ;
QUIT;

[VarunD]

Thank you for your reply.

I do not get an error now but the output data still has missing value.

It is not getting replaced by 'Unspecified'.

[novinosrin]

Proc SQL;
Create Table Current2(drop=_description) AS
Select *,coalescec(_Description, 'Unspecified') as Description
FROM Current1(rename=(description=_description));
QUIT;

Eventually there will be a day exclusive proc sql users will kill case when with ifc/ifn and coalesce c/n

EDITed to include a minor modification : rename and drop

[VarunD]

Thank you for your reply.

I do not get an error now but the output data still has missing value.

It is not getting replaced by 'Unspecified'.

[VDD]

Try a datastep like I have posted.  Sometimes a datastep is the tool to use

[novinosrin]

Using @VDD sample

data current1;
infile cards missover;
input Description $11. ;
cards;
Unspecified
nspecified
specified
Unspecified

Unspecified
;
Proc SQL;
Create Table Current2 AS
Select coalescec(Description, 'Unspecified') as Description
FROM Current1 ;
QUIT;

[VarunD]

Okay. This works when I only select the description variable. But when I do Select * , the missing values in description are still missing.

[novinosrin]

Yes i noticed that and i made an edit to the previous post. here you go again. I made a mistake to post before sipping a coffee and hence I missed to noitce the *

Proc SQL;
Create Table Current2(drop=_description) AS
Select *,coalescec(_Description, 'Unspecified') as Description
FROM Current1(rename=(description=_description));
QUIT;

[VarunD]

This is Perfect !

Happy Holidays to you.

[VDD · Accepted solution]

using a datastep

data current1;
infile cards missover;
input Description $11. ;
cards;
Unspecified
nspecified
specified
Unspecified

Unspecified
;
/* fails current2 */
Proc SQL;
Create Table Current2 AS
Select *,
Case when Description eq ' ' then Description eq 'Unspecified'
else Description eq Description
end as Description
FROM Current1 ;
QUIT;

data current3;
	set current1;
	if Description = '' then Description = 'Unspecified';
	else Description = Description;
run;

Sometimes a datastep may be the tool to use. 

[VarunD]

Thanks. This worked. But I don't understand why it won't work with Proc SQL.
Anyway. Appreciate your help.
Happy Holidays!

[VDD]

and there you have it you can not select the variable you are assigning how you were trying to 

data current1;
infile cards missover;
input Description $11. ;
cards;
Unspecified
nspecified
specified
Unspecified

Unspecified
;
/* fails */
Proc SQL;
Create Table Current2 AS
Select *,
Case when Description eq ' ' then Description eq 'Unspecified'
else Description eq Description
end as Description
FROM Current1 ;
QUIT;
/* fails */
Proc SQL;
Create Table Current2a AS
Select *,
Case when Description eq ' ' then  'Unspecified'
else Description
end as Description
FROM Current1 ;
QUIT;
/* fails */
Proc SQL;
Create Table Current2b AS
Select *,coalescec(Description, 'Unspecified') as Description
FROM Current1 ;
QUIT;
/* works */
Proc SQL;
Create Table Current2c AS
Select coalescec(Description, 'Unspecified') as Description
FROM Current1 ;
QUIT;
/* works */
data current3;
	set current1;
	if Description = '' then Description = 'Unspecified';
	else Description = Description;
run;

[Tom]

Your WHEN clause has a numeric value (the result of the EQ operator) and your ELSE clause has the character variable.

proc sql;
create table current2 as
  select *
       , case when description eq ' ' then 'unspecified'
              else description
         end as description
  from current1
;
quit;

[ballardw]

And an approach using a FORMAT which would avoid the conditional entirely.

proc format library=work;
value $junk
' '='Unspecified';
;
run;

data junk;
  input x $10.;
  y= put(x,$junk.);
datalines;
valid 
invalid
something
.
anything
;
run;

proc sql;
   create table junk2
   as select put(x,$junk.)
   from junk;
quit;

This would have one advantage that it should work with nearly any character variable with missing values though in some circumstances such as a data step where the declared variable length isn't long enough to hold "Undescribed" it will get truncated.