BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

12 REPLIES 12
art297
Opal | Level 21

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__
Jade | Level 19

Will that format affect the summary statistics?

art297
Opal | Level 21

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__
Jade | Level 19

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

podarum
Quartz | Level 8

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

art297
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Super User

Hi. Art.

Why not use

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

Ksharp

art297
Opal | Level 21

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
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4399 views
  • 0 likes
  • 6 in conversation