Replace missing values with 0

[NishunkSaxena]

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

[Ksharp]

proc stdize data=x reponly missing=0;

run;

[NishunkSaxena]

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

[LinusH]

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_null__]

Zero is a number.

[loredana_cornea · Accepted solution]

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

[CTorres]

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

[Haikuo]

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

Haikuo