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

[bncoxuk]

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.

[ieva · Accepted solution]

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;

[bncoxuk]

Thanks for sharing good idea.

[lordfox84]

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 ;

[fetcs74]

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;

[ChrisHemedinger]

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

[teeAepi]

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?

[data_null__]

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;

[bncoxuk]

Thanks for your consistent help.

[fedora_ed_sc_gov]

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

[Tim_Taylor]

What about "options missing=0"?

[ballardw]

---

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

[LauraJean]

Extremely helpful solution!

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

Thanks! 

[ChrisGermain]

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

[PeterClemmensen]

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

Use a var statement in PROC STDIZE