topic proc means in SAS Programming

proc means

LanMin — Sun, 18 Aug 2013 20:33:01 GMT

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!

Re: proc means

Reeza — Sun, 18 Aug 2013 21:10:39 GMT

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;

Re: proc means

Astounding — Sun, 18 Aug 2013 21:27:15 GMT

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.

Re: proc means

Peter_C — Mon, 19 Aug 2013 15:19:58 GMT

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

Re: proc means

LanMin — Tue, 20 Aug 2013 00:29:36 GMT

Thanks to all of you!