topic Re: How to calculate the average of some specific rows in one column in SAS Programming

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

Phil_NZ — Thu, 23 Jul 2020 19:57:47 GMT

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.

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

PGStats — Thu, 23 Jul 2020 20:17:27 GMT

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

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

Reeza — Thu, 23 Jul 2020 20:32:06 GMT

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

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

FreelanceReinh — Thu, 23 Jul 2020 20:34:33 GMT

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.

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

Phil_NZ — Thu, 23 Jul 2020 20:42:17 GMT

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

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

PGStats — Thu, 23 Jul 2020 20:55:41 GMT

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