Proc Means and zero values

[deleted_user]

Hi,
i have written the following code

proc means data = dataset1 EXCLNPWGT;
class Strategy;
var VALUE;
output out = datasetResults;
run;

I have alot of zeros in the value column and they are showing in the results, should the EXCLNPWGT option not ignore the zeroes and show me a true value?

Any help would be great

Cheers
Mike

[sbb]

There is topic-reference info at the SAS support http://support.sas.com/ website and a link to the latest version discussion is provided. Suggest you share some results, not just SAS code, for feedback.

Scott Barry
SBBWorks, Inc.

SAS Procedures Guide, Results: MEANS Procedure
Missing Values
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473538.htm

Recommended Google advanced search argument, this topic/post:
means zero value site:sas.com

[MikeZdeb]

hi ... the option EXCLNPWGT will look at the variable in a WEIGHT statement

from on-line DOC ... "EXCLNPWGT excludes observations with nonpositive weight values (zero or negative) from the analysis"

it does not say with zero values of the analysis variable

if you want to exclude observations with zero values for the variable VALUE, how about using a WHERE statement ...
[pre]
proc means data = dataset1;
where value ne 0;
class Strategy;
var VALUE;
output out = datasetResults;
run;
[/pre]
for example, you get two different answers with this ...
[pre]
data test;
input x y @@;
datalines;
0 1 2 1 3 1 4 0
;
run;

title 'EXCLUDE ZERO WEIGHTS';
proc means data=test EXCLNPWGTS;
var x;
weight y;
run;

title 'EXCLUDE ZERO VALUES';
proc means data=test;
where x ne 0;
var x;
run;

EXCLUDE ZERO WEIGHTS
N Mean Std Dev Minimum Maximum
3 1.6666667 1.5275252 0 3.0000000

EXCLUDE ZERO VALUES
N Mean Std Dev Minimum Maximum
3 3.0000000 1.0000000 2.0000000 4.0000000
[/pre]

[deleted_user]

thanks for the help mike, simple when you think about it i suppose. I was hoping to do this for quite a few columns so hoped that there was an easier solution as i imagine thi swill not work on many columns.

looks like i will have to write a macro to process each column individually

Cheers
for the suggestion
Mike

[data_null__]

How about creating a view that changes all the zeros to missing. Then you can still use one call to proc means for all vars. No macros involved.

[pre]
data no0 / view=no0;
set ....;
array _0

varA-numeric-Varz;
do _n_ = 1 to dim(_0);
if _0[_n_] eq 0 then _0[_n_] = .;
end;
run;
[/pre]

[deleted_user]

thanks for your code data _null_, it has been used to remove the zero values. I ended up building a macro to do each column individually as i could get the proc means to give me the answer required. I was also informed that our sas licenses are not going to be renewed so didn't want to spend too much time learning anything new..... gutted!!

[Peter_C]

wonder how they'll get the reports they need
without SAS

[Rathod]

Hi, I have a very simple question;

in case I am using multiple variables in var statement of proc means and I want to calculate average for all of them based on non zero values then How should approach the where statement in the proc means ?

[RW9]

Hi,

Please note that the post is 4 years old - start a new post.  In answer to your question, why not go with Data _null_;'s suggestion of using arrays:

data have;
  a=1; b=4; c=0; d=3; output;
  a=4; b=4; c=1; d=5; output;
run;
data inter (keep=res1-res4);
  set have;
  array var{4} a b c d;
  array res{4};
  do i=1 to 4;
    if var{i}=0 then res{i}=.;
    else res{i}=var{i};
  end;
run;

proc means data=inter;
  var res1-res4;
  output out=test;
run;

[Paige]

I caution you that it may not be a valid statistical analysis to exclude zeros from your mean values — of course, you may have a valid reason for doing so, but you haven't stated this.

[deleted_user]

thanks for the advice paige, the zeros had been removed at the request of the customer as they would not have any history with the product.