proc means

[LanMin]

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!

[Reeza]

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;

[Astounding · Accepted solution]

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.

[Peter_C]

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

[LanMin]

Thanks to all of you!