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

Posted in reply to NishunkSaxena

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: 10,041

Re: Replace missing values with 0

Posted in reply to NishunkSaxena

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,434

Re: Replace missing values with 0

Posted in reply to NishunkSaxena

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,799

Re: Replace missing values with 0

Posted in reply to NishunkSaxena

Zero is a number.

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

Re: Replace missing values with 0

Posted in reply to NishunkSaxena

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

Posted in reply to loredana_cornea

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,156

Re: Replace missing values with 0

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

Haikuo

🔒 This topic is solved and locked.

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

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