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

zeros before performing calculation, does anyone have an idea how to

do this. Thanks

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;

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;

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;

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

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

data need (drop = i);

set have;

array num (*) _numeric_;

do i = 1 to dim(num);

if num(i) = . then num(i)=0;

end;

run;

set have;

array num (*) _numeric_;

do i = 1 to dim(num);

if num(i) = . then num(i)=0;

end;

run;

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

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:

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:

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

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

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

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