topic Re: Must be a better way to do this (missing values) in SAS Procedures

Must be a better way to do this (missing values)

GVeers — Tue, 11 Aug 2009 21:34:10 GMT

Hey all, I have an Access database that with a number of fields that use "-9999" as a "missing" value. I am going to read the data into SAS using Proc Import (I'm slightly scared of other methods) and then use a Data statement to translate the -9999 to the missing character.

This seems like a clunky way to do things, particularly with the sheer number of variables that are set up in this fashion. Is this the best way to do it, or is there another way (maybe ditch the Proc Import or streamline the Data statement)?

Thank you very much in advance for any help you can offer.

Re: Must be a better way to do this (missing values)

sbb — Tue, 11 Aug 2009 22:02:54 GMT

Here is some DATA step code to consider using, an explicit array to address numeric variables only:

ARRAY a_num (*) _numeric_;
do i=1 to dim(a_num);
if = -999 then = .;
end;

Scott Barry
SBBWorks, Inc.

Re: Must be a better way to do this (missing values)

GVeers — Wed, 12 Aug 2009 13:14:10 GMT

I see. Thank you very much for the reply.

Re: Must be a better way to do this (missing values)

ChrisNZ — Thu, 13 Aug 2009 05:38:03 GMT

you can use Scott's code this way:

ARRAY a_num (*) _numeric_;
do i=1 to dim(a_num);
if a_num(i) = -9999 then a_num(i) = .;
end;

and all numeric variables are handled automatically without your having to name them.

Re: Must be a better way to do this (missing values)

ballardw — Tue, 18 Aug 2009 23:06:33 GMT

I would seriously look into creating one or more INFORMATS using Proc Format and INVALUE.

Something like this would set all values less than 0 to missing. The only issue is assigning the informat while using Proc Import. (Hint the log should contain a bunch of INFORMAT, FORMAT statements after the first run. Copy the code from the Log to the editor and apply as needed)

Proc format;
invalue groupval 0- high= _same_
other= .;
run;