Replacing blank value to 0

[yichentian226]

Hello,

I am trying to replace all the blank values of Bounceflag to 0, here's what I wrote: 

proc sql;
create table withbounce_new as
select withbounce.*,
case when bounceflag='' then bounceflag='0' end
from withbounce;
quit;

However SAS is showing me this (see screenshot), it created a new variable for me but I actually only wanted to replace all the blank values of Bounceflag to 0. Thanks in advance!

[Reeza]

You didn't give the new variable a name. Once you've done a calculation you need to assign it to the new variable name.

case when ...end as bounceflag

[yichentian226]

Thanks! However as I wrote case when bounceflag='' then bounceflag='0' end as bounceflag, I got this, blank space still blank...

[PaigeMiller]

case when missing(bounceflag) then '0' end as bounceflag

[yichentian226]

Hey thanks for helping again, appreciated! But I tried your code still it's blank space Im not sure..

[PaigeMiller]

Show us the LOG (the entire log for the PROC SQL step, including the code as it appears in the log, plus any NOTEs, WARNINGs or ERRORs for this step), with nothing chopped out.

[yichentian226]

1 %_eg_hidenotesandsource;
5 %_eg_hidenotesandsource;
29
30
31 proc sql;
32 create table withbounce_new as
33 select withbounce.*,
34 case when missing(bounceflag) then 0 end as bounceflag
35 from withbounce;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
WARNING: Variable bounceflag already exists on file WORK.WITHBOUNCE_NEW.

So I guess I need to create a new variable and drop the original bounceflag? 

[novinosrin]

Hi @yichentian226  try-

case when missing(bounceflag) then '0'  else bounceflag end as bounceflag

example-

data have ;
 do bounceflag='test',' ','test';
  output;
 end;
run;
proc sql;
 create table want as 
 select case when missing(bounceflag) then '0'  else bounceflag end as bounceflag
 from have;
quit;

[yichentian226]

proc sql;
create table withbounce_new as
select withbounce.*,
case when missing(bounceflag) then '0' else '1' end as bounceflag_
from withbounce;
quit;

i did this and it worked thanks!!

[CurtisMackWSIPP]

The COALESCEC function it the easiest way:

data withbounce;
  retain bounceflag '';
  do i = 1 to 10;
    output;
  end;
run;

proc sql;
create table withbounce_new as
select withbounce.*,coalescec(bounceflag,'0') as bounceflag_
from withbounce;
quit;

[ChrisNZ]

You cannot create a table with a column whose name is repeated.

This is indicated by the log message

WARNING: Variable bounceflag already exists on file WORK.WITHBOUNCE_NEW.

So either 1) create a new column, or 2) don't use select * when you clean the values: list the columns and do the cleaning where needed