how to take average of non 0 values

Reply
Frequent Contributor
Posts: 129

how to take average of non 0 values

Hi,

I want to take an average within individual category when the value is > 0

as an example, my table looks like this

Category     Day     Value

A               June1     10

A               June2     20

A               June3     0

If I do

select Category, average(value) as avg_val from table_a group by Category

then I get:

A     10

what I want is to take the average of the first two and ignore the last one as the value is 0... my desired output is as follows

A      15

Super Contributor
Posts: 349

Re: how to take average of non 0 values

Hi,

Try this..

proc sql;

create table want as

select Category, avg(value) as avg_val from have

where value >0

group by Category;

quit;

Thanks,

Shiva

Super User
Super User
Posts: 6,499

Re: how to take average of non 0 values

Might be easiest to just recode so that zero is stored as missing rather than a valid number.  Then PROC MEANS or any other normal statement to calculate statistics will work as you want.  You might create a view to do this to preserve your original table.

data miss0 / view=miss0;

  set have;

  if value = 0 then value=.;

run;

proc means data=miss0;

class category;

var value;

run;

Ask a Question
Discussion stats
  • 2 replies
  • 232 views
  • 0 likes
  • 3 in conversation