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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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. 

View solution in original post

14 REPLIES 14
Jagadishkatam
Amethyst | Level 16

please try

 

Proc SQL;
Create Table Current2 AS
Select *,
Case when Description eq ' ' then  'Unspecified'
else Description
end as Description
FROM Current1 ;
QUIT;
Thanks,
Jag
VarunD
Obsidian | Level 7

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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

novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7
Okay. This works when I only select the description variable. But when I do Select * , the missing values in description are still missing.
novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7

This is Perfect !

Happy Holidays to you.

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Obsidian | Level 7

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

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Super User Tom
Super User

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
Super User

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.

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 3433 views
  • 1 like
  • 6 in conversation