topic Re: replace 0's with missing in SAS Procedures

replace 0's with missing

podarum — Wed, 02 Nov 2011 20:42:47 GMT

I'd like to get an average from some data but not count the 0's

HAVE

data miss;
input a b c;

cards;

12 0 36

0 0 20

30 29 0

1 30 4

55 0 0

0 12 0

run;

WANT

12 . 36

. . 20

30 29 .

1 30 4

55 . .

. 12 .

Thanks

replace 0's with missing

art297 — Wed, 02 Nov 2011 20:49:30 GMT

One way to do it:

proc format;

invalue zeros

0=.;

run;

data miss;

input (a b c) (zeros2.);

cards;

12 0 36

0 0 20

30 29 0

1 30 4

55 0 0

0 12 0

;

replace 0's with missing

data_null__ — Wed, 02 Nov 2011 22:37:01 GMT

Will that format affect the summary statistics?

Re: replace 0's with missing

art297 — Wed, 02 Nov 2011 22:52:14 GMT

Will that INformat affect summary statistics? Appears to affect them for me. Although, it definitely works better if one uses the correct structure for an iinput statement (which I didn't):

proc format;

invalue zeros

0=.;

run;

data miss;

informat a b c zeros2.;

input a b c;

cards;

12 0 36

0 0 20

30 29 0

1 30 4

55 0 0

0 12 0

;

proc means data=miss;

var a b c;

run;

Results in:

The MEANS Procedure

Variable N Mean Std Dev Minimum

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

a 4 24.5000000 23.5867194 1.0000000

b 3 23.6666667 10.1159939 12.0000000

c 3 20.0000000 16.0000000 4.0000000

Re: replace 0's with missing

data_null__ — Wed, 02 Nov 2011 23:42:06 GMT

I wasn't paying attention. As you have demonstrated it does work.

Re: replace 0's with missing

podarum — Thu, 03 Nov 2011 02:03:29 GMT

thanks guys, but how can I apply this new format to a set with many columns, I'm assuming using _ALL_ somehow?

Re: replace 0's with missing

art297 — Thu, 03 Nov 2011 02:55:04 GMT

Three of the possibilities are using _all_, _numeric_ or a variable list (e.g., a--c in the example). But the informat will have to come AFTER the input statement. E.g.:

data miss;

input a b c;

informat _all_ zeros2.;

cards;

12 0 36

0 0 20

30 29 0

1 30 4

55 0 0

0 12 0

;

Re: replace 0's with missing

podarum — Thu, 03 Nov 2011 03:23:46 GMT

I'm trying using a set statement and it's not converting the 0's to .

data test;

set data;

informat _ALL_ Zeros2.;

run;

Re: replace 0's with missing

art297 — Thu, 03 Nov 2011 03:32:31 GMT

Different scenario .. different code needed. Try something like:

proc format;

invalue zeros

0=.;

run;

data miss;

input a b c;

cards;

12 0 36

0 0 20

30 29 0

1 30 4

55 0 0

0 12 0

;

data miss;

set miss;

array all(*) a--c;

do i=1 to dim(all);

all(i)=input(put(all(i),3.),zeros3.);

end;

run;

proc means data=miss;

var a b c;

run;

Re: replace 0's with missing

Ksharp — Thu, 03 Nov 2011 09:15:28 GMT

Hi. Art.

Why not use

if all(i) =0 then all(i)=.;

Ksharp

Re: replace 0's with missing

art297 — Thu, 03 Nov 2011 12:47:45 GMT

Because I'm lazy and had already created the hammer (i.e., the informat) earlier in the day. Obviously, both will end up with the same result.

Re: replace 0's with missing

Linlin — Thu, 03 Nov 2011 14:04:03 GMT

data miss;
input a b c;
cards;
12 0 36
0 0 20
30 29 0
1 30 4
55 0 0
0 12 0
run;

data want(drop=i);
set miss;
array _v(*) _numeric_;
   do i=1 to dim(_v);
      if _v(i)=0 then _v(i)=.;
   end;
run;

Re: replace 0's with missing

MikeZdeb — Thu, 03 Nov 2011 15:57:21 GMT

hi ... fyi (may be useful) ... when using an INPUT function with a numeric variable, the CAT function also avoids

the LOG message about numeric-to-character conversion ...

all(i)=input(cat(all(i)),zeros.);

versus ...

all(i)=input(put(all(i),3.),zeros3.);