topic Re: Replacing outliers with missing values in SAS Programming

Replacing outliers with missing values

PGStats — Sat, 26 Jan 2013 22:34:06 GMT

Hello wise people, I wonder if there is a better way to remove outliers from a dataset than this. I would particularly like to get rid of the special missing value.

Dataset myData contains variables date and station which identify observations uniquely. It also contains many numeric variables such as cond, c_org_diss, C_inor_diss, etc. I create another dataset outliersList with variables date and station and the name of the variable which should be set to missing. Here is what I have :

data outliersList;
format date yymmdd10.;
length var $32;
input date :yymmdd10. station $ var $;
value = .X; /* Special missing value */
datalines;
2009-07-17 BSF-1 C_inor_diss
2009-07-17 BSF-1 C_org_diss
2009-07-17 BSF-2 C_inor_diss
2009-07-17 BSF-2 C_org_diss
2010-05-17 BSF-3 cond
2010-07-07 BSF-2 C_inor_diss
2010-07-07 BSF-2 C_org_diss
;

proc transpose data=outliersList out=outliersUpdate(drop=_NAME_);
by date station;
id var;
var value;
run;

data myData;
update myData outliersUpdate;
by date station;
run;

Re: Replacing outliers with missing values

Linlin — Sat, 26 Jan 2013 23:43:27 GMT

Hi PG,

sorry,I am not wise enough to understand your question:smileysilly::smileyconfused::smileyblush:. You say " I would particularly like to get rid of the special missing value.", then you let value = .X; /* Special missing value */.

Don't you get a lot of .X after you run your code?

data myData;

update myData outliersUpdate;

by date station;

run;

Re: Replacing outliers with missing values

PGStats — Sun, 27 Jan 2013 00:42:39 GMT

Hi Linlin, I get .X only at the places where outliers were removed. But I would rather have "normal" missing values in those places. - PG

Re: Replacing outliers with missing values

Tom — Sun, 27 Jan 2013 04:08:18 GMT

In your first step replace the .X with regular missing. Then use the UPDATEMODE=nomissingcheck option on the UPDATE statement.

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a002645456.htm

Re: Replacing outliers with missing values

PGStats — Sun, 27 Jan 2013 04:26:10 GMT

Thanks Tom, but that would'n work. It would create an update dataset filled with missing values, the ones corresponding to my desired changes and the ones created by proc transpose. The update step would then zap all variables mentioned in the update dataset for all observations that match. That's not what I want. I want to zap only one variable per line in the update dataset. That's why I use the special missing value; it is treated differently than the missing values generated by transpose. - PG

Re: Replacing outliers with missing values

Tom — Sun, 27 Jan 2013 04:28:41 GMT

You can just replace the .X with . by looping over all of your numeric variables.

data want ;

update master transactions ;

array x _numeric_;

do over x ; if x=.X then x=.; end;

run;

But when we used to use actual transaction datasets 30 years ago we always used special missing ._ (dot underscore) to convert a value to missing. Checking the documentation you can see why:

If you want the resulting value in the master data set to be a regular missing value, use a single underscore (_) to represent missing values in the transaction data set. The resulting value in the master data set will be a period (.) for missing numeric values and a blank for missing character values.

Re: Replacing outliers with missing values

PGStats — Sun, 27 Jan 2013 05:00:12 GMT

You're right. That is a simple solution. Thanks!

Re: Replacing outliers with missing values

data_null__ — Sun, 27 Jan 2013 14:39:37 GMT

I'm confused. Wouldn't it be better for PGStats to just change VALUE=.X to VALUE=._; Then you don't need loop on array.

Re: Replacing outliers with missing values

PGStats — Mon, 28 Jan 2013 03:07:21 GMT

Well, this special missing values business can rapidly become very messy. I would rather avoid it entirely. Tom's idea brings me back to safe ground and I like that. Look at this :

data _null_;

y = ._;

if y = .

then put "Yippee!";

else put "Bummer!";

run;

They would look the same, have the same meaning and yet not be equal. A recipe for cryptic errors. Frightening!

Re: Replacing outliers with missing values

data_null__ — Mon, 28 Jan 2013 11:47:16 GMT

._ updates non missing to . "standard missing" in the new master.

With regards to you out of context example.

17         data _null_;
18            y = ._;
19            if missing(y)
20               then put "Yippee!";
21               else put "Bummer!";
22            run;

Yippee!

Re: Replacing outliers with missing values

Tom — Mon, 28 Jan 2013 14:50:45 GMT

If you use ._ in the TRANSACTION file the values they replace will become regular missing in the result dataset. So it saves having to convert them yourself. You want to be able to distinguish between the missing and the special missing in the transaction dataset.

Re: Replacing outliers with missing values

PGStats — Mon, 28 Jan 2013 14:56:11 GMT

Thank you DN and Tom for your wisdom! Now I get it! - PG