## proc means

Solved
Frequent Contributor
Posts: 102

# proc means

Dear all,

I am using proc means to average values across multiple observations for each year. (see attached data). one complication is variable Horizon, some records have a special code -99, indicating this value might be missing or subject to error. In such cases, I want to average only those valid values,

e.g. For year_FPE=1996, avgHORI=(5/304+4.951+2.386)/3 (excluding -99)

How should I modify the code below ?

PROC MEANS DATA=id1300 NWAY noprint;

CLASS Security_ID   YEAR_FPE;

VAR horizon Specificity count;

OUTPUT OUT=want  MEAN=avgHORI avgSPEC avgcount;

RUN;

thanks!

Accepted Solutions
Solution
‎08-18-2013 05:27 PM
Super User
Posts: 6,751

## Re: proc means

While Reeza's solution works, you would be well advised to change your data.  SAS supports special missing values, such as:

if horizon = -99 then horizon=.A;

There are 27 special missing values, .A through .Z, and ._ as well.

If you change the -99 to .A, the PROC MEANS will automatically throw them out without the need for a WHERE statement.  This becomes especially useful if you have two analysis variables.  For example, suppose COUNT could also be -99, but not necessarily on the same observations having HORIZON = -99.  Then there would be no WHERE statement that could remove the proper observations.  But if you have changed all -99 values to a special missing value, PROC MEANS would know enough to leave them out of the calculations.

Good luck.

All Replies
Super User
Posts: 23,661

## Re: proc means

Add in a where statement.

PROC MEANS DATA=id1300 NWAY noprint;

### where horizon ne -99;

CLASS Security_ID   YEAR_FPE;

VAR horizon Specificity count;

OUTPUT OUT=want  MEAN=avgHORI avgSPEC avgcount;

RUN;

Solution
‎08-18-2013 05:27 PM
Super User
Posts: 6,751

## Re: proc means

While Reeza's solution works, you would be well advised to change your data.  SAS supports special missing values, such as:

if horizon = -99 then horizon=.A;

There are 27 special missing values, .A through .Z, and ._ as well.

If you change the -99 to .A, the PROC MEANS will automatically throw them out without the need for a WHERE statement.  This becomes especially useful if you have two analysis variables.  For example, suppose COUNT could also be -99, but not necessarily on the same observations having HORIZON = -99.  Then there would be no WHERE statement that could remove the proper observations.  But if you have changed all -99 values to a special missing value, PROC MEANS would know enough to leave them out of the calculations.

Good luck.

Valued Guide
Posts: 2,191

## Re: proc means

Posted in reply to Astounding

If there is any concern about the inefficiency of re-writing all the data to take advantage of the "Astounding" idea, just create a VIEW to convert those -99 into special missings and implement that complex formula at the same time. For as PROC MEANS reads the view, the special values and formula will be calculated "on the fly".

Neat

Pete

Frequent Contributor
Posts: 102

## Re: proc means

Thanks to all of you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 334 views
• 6 likes
• 4 in conversation