BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NishunkSaxena
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
loredana_cornea
Obsidian | Level 7

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

7 REPLIES 7
Ksharp
Super User

proc stdize data=x reponly missing=0;

run;

NishunkSaxena
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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
data_null__
Jade | Level 19

Zero is a number.

loredana_cornea
Obsidian | Level 7

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

CTorres
Quartz | Level 8

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
Onyx | Level 15

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

Haikuo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 43723 views
  • 4 likes
  • 7 in conversation