Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-07-2016 11:56 AM
Frequent Contributor
Posts: 82

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

[ Edited ]

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;

 

View solution in original post


All Replies
Solution
‎07-07-2016 11:56 AM
Frequent Contributor
Posts: 82

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

[ Edited ]

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;

 

Frequent Contributor
Posts: 131

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

Thanks for sharing good idea.

N/A
Posts: 1

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

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 ;

Occasional Contributor
Posts: 10

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

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;
Community Manager
Posts: 2,762

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

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

New User
Posts: 1

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

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?

 

Respected Advisor
Posts: 3,777

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

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;

Frequent Contributor
Posts: 131

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

Thanks for your consistent help. Smiley Happy

Senior User
Posts: 1

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

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

New User
Posts: 1

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

What about "options missing=0"?
Super User
Posts: 10,500

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


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.

Contributor
Posts: 20

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

Extremely helpful solution!

 

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

 

Thanks! 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 91984 views
  • 19 likes
  • 11 in conversation