### sas >> How to replace missing values with zeros

My data has missing values across that I would like to replace with
zeros before performing calculation, does anyone have an idea how to
do this. Thanks

### sas >> How to replace missing values with zeros

Kojo,

Just include
options missing = 0;

Muthia Kachirayan

options missing = 0;
data a;
input a b;
cards;
12 23
10 .
20 .
30 20
. 30
50 .
. 70
;
run;

### sas >> How to replace missing values with zeros

If you have SAS/STAT software. PROC STDIZE can do this quickly and easily.

data work.allmiss;
call missing(a,b,c,d,e);
do _n_ = 1 to 10;
output;
end;
run;
proc stdize missing=0 reponly method=sum out=allZero;
var _numeric_;
run;
proc contents varnum;
proc print;
run;

### How to replace missing values with zeros

don't think that this is what Kojo is asking for. Try running

options missing = 0;
data a;
input a b;
c = a + b ;
cards;
12 23
10 .
20 .
30 20
. 30
50 .
. 70
run;
options missing = . ;
Proc print;
run;

The Options missing = changes the way that a missing value is printed
but does not actually change the way that it is stored. If you run the code
above, you will see a message that tells us that missing values were
created during an arithmetic operation. I.e., when you add a and b, the
result is missing if one or both of the values are missing. Unless you put
in the second options statement, the results of adding, say 5 and .
appears as 0 which is not the same as adding
5 + 0.

Rather, I would change the values to 0 as follows:

data a;
input a b;

array able _numeric_;
do i = 1 to dim(able);
if able(i) = . then able(i) = 0;
end;

c = a + b ;
cards;
12 23
10 .
20 .
30 20
. 30
50 .
. 70
run;

Proc print;
run;

Having said this, I must ask why you need to change missing values to 0.
Are missing values really responses that equal 0 or do they mean something.
else.

The results of SAS procs and functions will depend on whether you have
missing values and your results can be wrong if you improperly substitute
zeros. For example, if you have a series of weights but a couple are
missing and if you substitute 0 for the missing weights and then run a proc
means, the average will be the sum divided by the number of weights
including those that were missing. I doubt that this is what you would
want.

Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977

Muthia Kachirayan
<muthia.kachiraya
XXXX@XXXXX.COM > To
Sent by: "SAS(r) XXXX@XXXXX.COM
Discussion" cc
< XXXX@XXXXX.COM
GA.EDU> Subject
Re: How to replace missing values
with zeros
05/17/2007 12:01
PM

Muthia Kachirayan
<muthia.kachiraya
XXXX@XXXXX.COM >

On 5/17/07, Kojo < XXXX@XXXXX.COM > wrote:

Kojo,

Just include
options missing = 0;

Muthia Kachirayan

options missing = 0;
data a;
input a b;
cards;
12 23
10 .
20 .
30 20
. 30
50 .
. 70
;
run;

-----------------------------------------
CONFIDENTIALITY NOTICE: This electronic message contains
information which may be legally confidential and/or privileged and
does not in any case represent a firm ENERGY COMMODITY bid or offer
relating thereto which binds the sender without an additional
express written confirmation to that effect. The information is
intended solely for the individual or entity named above and access
by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, distribution, or use of the
contents of this information is prohibited and may be unlawful. If
reply immediately to the sender that you have rece

### How to replace missing values with zeros

data need (drop = i);
set have;
array num (*) _numeric_;
do i = 1 to dim(num);
if num(i) = . then num(i)=0;
end;
run;

### How to replace missing values with zeros

SAS tends to do the right thing when you have missing values in your
data. So are you really sure that you *should* make this conversion?
Unless the missing values are really supposed to be zeroes due to some
prior disaster with database management, I would recommend *not*
doing this.

Perhaps. if you write back to SAS-L and explain why you want to do this,
and why you think SAS will not do the right thing with your missing
values, then someone here can solve your problem.

HTH,
David
_________________________________________________________________
PC Magazine 2007 editorschoice for best Web mailward-winning Windows
Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_pcmag_0507

### How to replace missing values with zeros

I think there is one aspect that has not been covered explicitly
so far.
"before performing calculation" can mean different things.
Calculations that SAS does within its PROCs or calculations that
we do within data steps or as part of SQL statements, where
statements and the like.

So while

total = a + b + c ;

yields a missing value whenever one or more of a, b or c are
missing

total = sum ( a , b , c , 0 ) ;

yields a non-missing value even when all of a, b and c are
missing.

So my suggestion to Kojo is to check the available functions with
a focus on this aspect.

Robert Bardos
Ansys AG, Zurich, Switzerland

David Cassell (amongst others) replied in part:

### How to replace missing values with zeros

Kojo,
could you please tell us, where 0 instead of missing values make sense in
There is no problem to replace that . with 0 like:

if a=. then a=0;

or

a=sum(a,0);

but is that really, what you want? Let's say, you have a dataset with some
of your customer's birthdates and you want to send them a mail for
congratulations any year and a present any 10 years. What do you think how
many of your customers are now 47 years old and where born at 1th of
january? I think, all of them where you DONT KNOW the birthdate! That is
the meaning of "missing" = "DON'T KNOW". If it is 0, that is NOT "don't
know"!
So I can't imagine that there is a environment where replacing missing by
0 makes any sense, but you might bring some light in my darkness...
Gerhard

```Hi all,
I need to fill missing values with the values of the previous
observation by the same group.
The code bellow provides the answer. However I have about 10 variables
I have to fill so I wonder if there is a more
efficiant solution.
Thanks,
Josip

data xy;
input x x1 x2 y \$;
datalines;
1 2  3 A
2 3  4 A
3 .  . A
4 5  6 A
5 .  . B
. .  . B
7 8  9 B
8 9 10 B
;
proc sort data=xy;
by y;
run;
data xy1;
set xy;
array v(3) x x1 x2;
retain _x _x1 _x2;
by y;
if first.y then do;
_x=x; _x1=x1; _x2=x2;
end; else do;
if missing(x) or missing(x1) or missing(x2) then do;
x=_x; x1=_x1; x2=_x2;
end; else do;
_x=x; _x1=x1; _x2=x2;
end;
end;
drop _x _x1 _x2;
run;
proc print data=xy1;
run;

```

```Hi Everyone,

I have a data set that looks like this:

rep 	count
1	1
1	1
1	3
1	3
1	4
2	4
2	4
2	4
2	4
2	6
3	1
3	2
3	3
3	3
3	3

I wish to create a new column called count_adj where (within each rep)
count_adj=count, unless count=the previous value of count then
count_adj=0. In other words, within each rep, I want to replace
redundant values of count with zeros so that I have something that
looks like this:

1	1	1
1	1	0
1	3	3
1	3	0
1	4	4
2	4	4
2	4	0
2	4	0
2	4	0
2	6	4
3	1	1
3	2	2
3	3	3
3	3	0
3	3	0

Thanks, Gigi
```

```Hi, If a "  value(missing value) appear,How caould I write program
so that I  replace the " by its lag value
Thanks

```

```Hi SAS-L,

I have the following data structure:

sim group x1  x2  x3
1    1    32  23  11
1    1    23   .  22
1    1    .   33  23
1    2    34  21  11
1    2    33  22   .
1    2    32  33  24
2    1    22  23  14
2    1    23  33   .
2    1    .   30  23
2    2    34  21  11
2    2    35   .  22
2    2    32  33  24

Clearly, the sim (simulation) variable will be much longer then two.
Also, the group sample size is much larger.  I would like to replace
each missing value with the mean of that particular column mean by
group and simulation run.  Is there a way to do this directly in SAS
IML.  Any help or suggestions would be much appreciated.

Thank you,

Song

```