topic Re: How to set all missing values to zero for all variables? in SAS Procedures

How to set all missing values to zero for all variables?

bncoxuk — Wed, 03 Aug 2011 10:59:41 GMT

Hi,

I am looking for an easy way to set all missing values to zero for all variables in a dataset. There are over 50 variables which takes too much work to do it manually.

How to set all missing values to zero for all variables?

ieva — Thu, 07 Jul 2016 15:56:39 GMT

Editor's note: This topic is very popular. Thanks to @ieva and @data_null__ for useful replies. We've consolidated those here to make it easier for future learners.

I suppose, you want to change all numeric variables. Try this:

data yourdata;
   set yourdata;
   array change _numeric_;
        do over change;
            if change=. then change=0;
        end;
 run ;

@ieva's solution works well with Base SAS. If you have SAS/STAT (most users do), then you can use PROC STDIZE as shared by @data_null__:

/* create test data set */
data missing;
   set sashelp.class;
   if mod(_n_,2) eq 1 then call missing(of _numeric_);
   run;

proc print;
   run;
proc stdize out=zeros reponly missing=0;
   run;
proc print;
   run;

How to set all missing values to zero for all variables?

data_null__ — Wed, 03 Aug 2011 11:22:32 GMT

You can do that with PROC STDIZE, too.

data missing;

set sashelp.class;

if mod(_n_,2) eq 1 then call missing(of _numeric_);

run;

proc print;

run;

proc stdize out=zeros reponly missing=0;

run;

proc print;

run;

How to set all missing values to zero for all variables?

bncoxuk — Wed, 03 Aug 2011 15:26:30 GMT

Thanks for sharing good idea.

How to set all missing values to zero for all variables?

bncoxuk — Wed, 03 Aug 2011 15:27:01 GMT

Thanks for your consistent help.

Re: How to set all missing values to zero for all variables?

lordfox84 — Fri, 24 Oct 2014 13:12:37 GMT

And of course for characters variables u Can replace _numeric_ with _character_ :

data yourdata;

set yourdata;

array change _character_;

do over change;

if change=. then change=0;

end;

run ;

Re: How to set all missing values to zero for all variables?

fedora_ed_sc_gov — Fri, 13 May 2016 15:30:55 GMT

Whoever posted the solution, this is awesome. Thank you!

Re: How to set all missing values to zero for all variables?

Tim_Taylor — Tue, 30 Aug 2016 20:03:17 GMT

What about "options missing=0"?

Re: How to set all missing values to zero for all variables?

ballardw — Wed, 31 Aug 2016 15:50:59 GMT

@Tim_Taylor wrote:
What about "options missing=0"?

If you need the value to be 0 such as for modeling then the value needs to be set. The Missing option only effects display such as in table views or proc displayed output, not data values.

Re: How to set all missing values to zero for all variables?

fetcs74 — Thu, 01 Sep 2016 09:39:47 GMT

Hello,

I know that it is an old topic, but I would like to add a new possible solution, using Proc LUA (please, note that I'm using 9.4 M2 and not M3);

/*Create example dataset*/
data missing;
set sashelp.class;
if mod(_n_,2) eq 1 then call missing(of _numeric_);
run;

/*The procedure itself*/
proc lua;
submit;
   local dsid = sas.open("work.missing","u") -- open in update mode            
   local vars = {}

   -- Iterate over the variables in the data set
   for var in sas.vars(dsid) do
      vars[var.name:lower()] = var               
   end

   -- Iterate over the rows of the data set   
	 while sas.next(dsid) do
	 	 for vname,var in pairs(vars) do
		 	if sas.is_missing(sas.get_value(dsid, vname)) then
				dsid:put_value(vname,0) 
				sas.update(dsid)
			end --End if
   end  --End for
	end --End while
  sas.close(dsid)
endsubmit;
run;

Re: How to set all missing values to zero for all variables?

ChrisHemedinger — Thu, 01 Sep 2016 15:55:17 GMT

Interesting! Good way to learn PROC LUA, and it looks like your approach updates the data "in place", at least conceptually.

Re: How to set all missing values to zero for all variables?

teeAepi — Sun, 06 Nov 2016 20:20:30 GMT

What if you want to set varibles to missing based on a condition.

Like, if ( X and Y and B and G and D and Y and Z)=. then all variables in this array group = 88

is that possible?

Re: How to set all missing values to zero for all variables?

LauraJean — Tue, 09 May 2017 13:06:20 GMT

Extremely helpful solution!

When I googled this topic this was one of the first posts that came up.

Thanks!

Re: How to set all missing values to zero for all variables?

ChrisGermain — Tue, 23 Jan 2018 02:37:04 GMT

How would i select specific variables only and replace the missing values with 0?

Re: How to set all missing values to zero for all variables?

PeterClemmensen — Tue, 23 Jan 2018 08:23:57 GMT

proc stdize out=zeros reponly missing=0;
   var MyVariable;
run;

Use a var statement in PROC STDIZE

Re: How to set all missing values to zero for all variables?

ChrisGermain — Tue, 23 Jan 2018 17:50:19 GMT

Thank you very much! One other question:

1) How can I replace a missing value for a different variable using the mean of a group?

I have missing values for a variable "area" and I want to replace the missing values with the mean of "area" by "neighborhood", where values are not missing.

Re: How to set all missing values to zero for all variables?

PeterClemmensen — Tue, 23 Jan 2018 18:44:24 GMT

Use PROC STDIZE as in the replace with zero case and use the MISSING=MEAN instead and the BY Statement to specify your By-Group