Help using Base SAS procedures

replace 0's with missing

Reply
Super Contributor
Posts: 395

replace 0's with missing

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

PROC Star
Posts: 7,363

replace 0's with missing

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

;

Respected Advisor
Posts: 3,777

replace 0's with missing

Will that format affect the summary statistics?

PROC Star
Posts: 7,363

Re: replace 0's with missing

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

Respected Advisor
Posts: 3,777

Re: replace 0's with missing

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

Super Contributor
Posts: 395

Re: replace 0's with missing

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

PROC Star
Posts: 7,363

Re: replace 0's with missing

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

;

Super Contributor
Posts: 395

Re: replace 0's with missing

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

data test;

set data;

informat _ALL_ Zeros2.;

run;

PROC Star
Posts: 7,363

Re: replace 0's with missing

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;

Super User
Posts: 9,681

Re: replace 0's with missing

Hi. Art.

Why not use

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

Ksharp

PROC Star
Posts: 7,363

Re: replace 0's with missing

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.

Valued Guide
Posts: 765

Re: replace 0's with missing

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

Super Contributor
Posts: 1,636

Re: replace 0's with missing

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;

Ask a Question
Discussion stats
  • 12 replies
  • 1621 views
  • 0 likes
  • 6 in conversation