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.
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.
Please provide us with data as text, not pictures, so that we can test our proposals.
Hi @PGStats , the data is added below, thank you.
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.
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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.