BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
10 REPLIES 10
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Rhodochrosite | Level 12
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
Not applicable
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__
Jade | Level 19
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
    Not applicable
    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
    Rhodochrosite | Level 12
    wonder how they'll get the reports they need
    without SAS
    Rathod
    Calcite | Level 5

    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
    Diamond | Level 26 RW9
    Diamond | Level 26

    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
    Quartz | Level 8
    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
    Not applicable
    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.

    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
    • 10 replies
    • 15929 views
    • 0 likes
    • 8 in conversation