Replace missing values with 0

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Replace missing values with 0

Hi All,

Any help would be appreciated.

I have a dataset which I have recieved from someone. I donot want to open it and dont know how many columns it have, I want to replace missing values with 0 in each column in it.

Thanks in advance


Accepted Solutions
Solution
‎06-19-2013 10:29 AM
Contributor
Posts: 31

Re: Replace missing values with 0

options missing = '0';     /*for missing numeric variables*/

data "database name";  /*for missing character variables, write this code as it is except the "database name" thing */

    set "database name";

    array ch(*) _character_;

    do _n_ = 1 to dim(ch);

    ch(_n_) = coalescec(ch(_n_), '0');

end;

run;

PS: Although you've replaced all missing character values with 0, this value will be considered a character and not the numeric value 0. It is impossible for a character variable to contain both character and numeric values at once. If you truly need the numeric 0 in the character variable you should convert the character variables to numeric ones by multiplying them by 1. Ex: new_charvariable = 1*charvariable;

Good luck and tell us if you've solved the problem Smiley Wink

View solution in original post


All Replies
Super User
Posts: 9,662

Re: Replace missing values with 0

proc stdize data=x reponly missing=0;

run;

Occasional Contributor
Posts: 12

Re: Replace missing values with 0

but this Stdize works only on Numeric values not on Character missing values

Super User
Posts: 5,254

Re: Replace missing values with 0

Any specific reason for not opening the table? :smileyconfused:

One way is to assign your column values to two arrays (one numeric - _NUMERIC_, and one char _CHARACTER_).

Then loop through the arrays and do coalesce/coalescec using 0/'0'.

Data never sleeps
Respected Advisor
Posts: 3,777

Re: Replace missing values with 0

Zero is a number.

Solution
‎06-19-2013 10:29 AM
Contributor
Posts: 31

Re: Replace missing values with 0

options missing = '0';     /*for missing numeric variables*/

data "database name";  /*for missing character variables, write this code as it is except the "database name" thing */

    set "database name";

    array ch(*) _character_;

    do _n_ = 1 to dim(ch);

    ch(_n_) = coalescec(ch(_n_), '0');

end;

run;

PS: Although you've replaced all missing character values with 0, this value will be considered a character and not the numeric value 0. It is impossible for a character variable to contain both character and numeric values at once. If you truly need the numeric 0 in the character variable you should convert the character variables to numeric ones by multiplying them by 1. Ex: new_charvariable = 1*charvariable;

Good luck and tell us if you've solved the problem Smiley Wink

Regular Contributor
Posts: 180

Re: Replace missing values with 0

Hi loredana,

According to the SAS Documentation, the MISSING system option does not replace missing values in numeric variables but instead "Specifies the character to print for missing numeric values." so I think that the correct solution for this request would be to use the same approach used to replace the character variables with the coalesce function:

data "database name";  /*for missing character variables, write this code as it is except the "database name" thing */
    set "database name";
    array ch(*) _character_;
    array nm(*) _numeric_ ;
    do _n_ = 1 to dim(ch);
    ch(_n_) = coalescec(ch(_n_), '0');
    end;

   do _n_ = 1 to dim(nm);

    nm(_n_) = coalesce(nm(_n_),0);

    end;

run;

CTorres

Respected Advisor
Posts: 3,124

Re: Replace missing values with 0

You are right. "options missing =" works the exact way "format" does, only global.

Haikuo

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 18858 views
  • 3 likes
  • 7 in conversation