sas >> How to replace missing values with zeros

by Kojo » Thu, 17 May 2007 23:52:40 GMT

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

by muthia.kachirayan » Fri, 18 May 2007 00:01:07 GMT



Kojo,

Just include
options missing = 0;

Can this help you?

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

by datanull » Fri, 18 May 2007 00:08:05 GMT

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;

sas >> How to replace missing values with zeros

by Nathaniel.Wooding » Fri, 18 May 2007 00:41:31 GMT

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


Please respond to
Muthia Kachirayan
<muthia.kachiraya
XXXX@XXXXX.COM >






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

Kojo,

Just include
options missing = 0;

Can this help you?

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
you have received this electronic transmission in error, please
reply immediately to the sender that you have rece

sas >> How to replace missing values with zeros

by foa2 » Fri, 18 May 2007 01:37:56 GMT

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

sas >> How to replace missing values with zeros

by davidlcassell » Fri, 18 May 2007 10:50:27 GMT


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

sas >> How to replace missing values with zeros

by bardos2 » Fri, 18 May 2007 17:10:23 GMT

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:

sas >> How to replace missing values with zeros

by gerhard.hellriegel » Fri, 18 May 2007 23:11:53 GMT

Kojo,
could you please tell us, where 0 instead of missing values make sense in
your calculations?
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

Similar Threads

1. Need to replace missing values with zeros in a varying number of rows and varying number of columns

2. replacing missing values with the values of the previous observation

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;

3. How to replace the Missing Value with the correct Value by

4. replace repeated values with zeros easy question!

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:

rep 	count	adj_count
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

I am sure this is an easy problem for SAS wizards. Please help!

Thanks, Gigi

5. Replace missing values

6. replace missing value

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

7. Replacing a Missing Value

8. Replacing Missing Values in IML

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