topic Re: Replace missing values with 0 in SAS Programming

Replace missing values with 0

NishunkSaxena — Wed, 19 Jun 2013 05:51:15 GMT

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

Re: Replace missing values with 0

Ksharp — Wed, 19 Jun 2013 06:18:52 GMT

proc stdize data=x reponly missing=0;

run;

Re: Replace missing values with 0

NishunkSaxena — Wed, 19 Jun 2013 09:23:47 GMT

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

Re: Replace missing values with 0

LinusH — Wed, 19 Jun 2013 11:12:36 GMT

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'.

Re: Replace missing values with 0

data_null__ — Wed, 19 Jun 2013 12:59:26 GMT

Zero is a number.

Re: Replace missing values with 0

loredana_cornea — Wed, 19 Jun 2013 14:29:54 GMT

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

Re: Replace missing values with 0

CTorres — Fri, 27 Jun 2014 15:22:16 GMT

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

Re: Replace missing values with 0

Haikuo — Fri, 27 Jun 2014 15:27:33 GMT

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

Haikuo