topic Re: Replacing blank value to 0 in SAS Programming

Replacing blank value to 0

yichentian226 — Thu, 24 Sep 2020 21:57:48 GMT

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!

Re: Replacing blank value to 0

Reeza — Thu, 24 Sep 2020 22:03:28 GMT

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

Re: Replacing blank value to 0

yichentian226 — Thu, 24 Sep 2020 22:08:05 GMT

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

Re: Replacing blank value to 0

PaigeMiller — Thu, 24 Sep 2020 22:10:37 GMT

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

Re: Replacing blank value to 0

yichentian226 — Thu, 24 Sep 2020 22:12:07 GMT

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

Re: Replacing blank value to 0

PaigeMiller — Thu, 24 Sep 2020 22:25:34 GMT

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.

Re: Replacing blank value to 0

yichentian226 — Thu, 24 Sep 2020 22:31:11 GMT

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?

Re: Replacing blank value to 0

novinosrin — Thu, 24 Sep 2020 22:35:23 GMT

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;

Re: Replacing blank value to 0

yichentian226 — Thu, 24 Sep 2020 22:34:50 GMT

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!!

Re: Replacing blank value to 0

CurtisMackWSIPP — Thu, 24 Sep 2020 22:45:28 GMT

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;

Re: Replacing blank value to 0

ChrisNZ — Fri, 25 Sep 2020 00:17:41 GMT

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