topic Re: Case When Error in SAS Programming

Case When Error

VarunD — Fri, 21 Dec 2018 17:03:10 GMT

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.

Re: Case When Error

Jagadishkatam — Fri, 21 Dec 2018 17:06:21 GMT

please try

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

Re: Case When Error

novinosrin — Fri, 21 Dec 2018 17:34:43 GMT


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

Re: Case When Error

VDD — Fri, 21 Dec 2018 17:26:26 GMT

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.

Re: Case When Error

VarunD — Fri, 21 Dec 2018 17:24:06 GMT

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

Re: Case When Error

VarunD — Fri, 21 Dec 2018 17:24:21 GMT

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

Re: Case When Error

VDD — Fri, 21 Dec 2018 17:27:34 GMT

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

Re: Case When Error

VarunD — Fri, 21 Dec 2018 17:29:39 GMT

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

Re: Case When Error

novinosrin — Fri, 21 Dec 2018 17:30:48 GMT

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;

Re: Case When Error

VarunD — Fri, 21 Dec 2018 17:34:19 GMT

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

Re: Case When Error

VDD — Fri, 21 Dec 2018 17:36:03 GMT

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;

Re: Case When Error

novinosrin — Fri, 21 Dec 2018 17:36:18 GMT

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;

Re: Case When Error

VarunD — Fri, 21 Dec 2018 17:40:18 GMT

This is Perfect !

Happy Holidays to you.

Re: Case When Error

Tom — Fri, 21 Dec 2018 18:17:28 GMT

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;

Re: Case When Error

ballardw — Fri, 21 Dec 2018 20:49:58 GMT

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.