replace 0's with missing

[podarum]

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

[art297]

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

;

[data_null__]

Will that format affect the summary statistics?

[art297]

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

[data_null__]

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

[podarum]

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

[art297]

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

;

[podarum]

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

data test;

set data;

informat _ALL_ Zeros2.;

run;

[art297]

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;

[Ksharp]

Hi. Art.

Why not use

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

Ksharp

[art297]

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.

[MikeZdeb]

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.);

[Linlin]

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;