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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ieva
Pyrite | Level 9

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

16 REPLIES 16
ieva
Pyrite | Level 9

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
Obsidian | Level 7

Thanks for sharing good idea.

lordfox84
Calcite | Level 5

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
Fluorite | Level 6

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
Community Manager

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

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
teeAepi
Calcite | Level 5

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

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
Obsidian | Level 7

Thanks for your consistent help. Smiley Happy

fedora_ed_sc_gov
Calcite | Level 5

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

Tim_Taylor
Calcite | Level 5
What about "options missing=0"?
ballardw
Super User

@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
Obsidian | Level 7

Extremely helpful solution!

 

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

 

Thanks! 

ChrisGermain
Fluorite | Level 6
How would i select specific variables only and replace the missing values with 0?
PeterClemmensen
Tourmaline | Level 20
proc stdize out=zeros reponly missing=0;
   var MyVariable;
run;

Use a var statement in PROC STDIZE

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 16 replies
  • 309847 views
  • 37 likes
  • 13 in conversation