How to calculate the average of some specific rows in one column

[Phil_NZ]

Hi SAS community,

I have the input as below:

Offtrack a little bit, this one is obtained after using the "sum" statement in proc means.

Back to my question, my column of interest is agg_amh. What I want is to have an average of all rows containing number values but not the first row (because the first row is the aggregate number already). In other words, I am asking how to count the number of rows containing "number" in column "agg_amh" (but the row (1)), then I will use the aggregate number 0.0003195096 divide to this number to get the average number.

In sum, my ulterior motive is to get the number of rows that contain number but the first row, in this case, we have 17 rows from row 6th to row 22nd. Then I want to divide 0.0003195096 to 17. The final number that I want is 0.0003195096/17=0.0000187946.

Many thanks.

[PGStats]

Please provide us with data as text, not pictures, so that we can test our proposals.

[Phil_NZ]

Hi @PGStats , the data is added below, thank you.

[Reeza]

Look at your _TYPE_ variable which allows you to group your data.

[FreelanceReinh · Accepted solution]

Hi @Phil_NZ,

Why don't you just apply PROC MEANS again, now to this summary dataset? The exclusion of the first row can be accomplished by a firstobs=2 dataset option or a WHERE condition (e.g. where _type_=1;). The MEAN statistic for variable agg_amh is the desired value (and the N statistic is the number of rows in question) since missing values of agg_amh are excluded by default.

[PGStats]

Here is how to, with proc SQL:

proc sql;
select sum(agg_amh) / count(agg_amh) as value format=32.12
from have
where _type_ = 1;
quit;

value
0.000018794681