Fluorite | Level 6

## Calculate multiple averages on same column

I'm hoping to find some method which will allow me to calculate multiple averages on the same columns for different time periods, and I'm not sure how or if it can be done.

Some example data would be as follows:

``````data have;
format Date mmddyy10.;
input Date STR_3 STR_4 STR_7 STR_8;
datalines;
18917 . 17386 . 6756
18924 . 20913 . 9101
18931 . 19672 . 8734
18938 . 20362 . 9175
18945 . 21325 . 8749
18952 . 21129 . 8932
18959 . 21540 . 8374
18966 17714 19860 . 9645
18973 14983 22248 . 9055
18981 12833 20781 . 8709
18988 17347 21402 . 8886
18995 17295 22218 4400 9738
19002 17337 21577 3813 8830
19009 17402 23090 5052 10119
19016 17395 23426 5001 9480
19024 14114 24574 4361 9702
;
run;``````

And it looks like this:

Let's say I want to do the following:

1. Calculate an average of all observations for STR_3.
2. Calculate an average, using STR_4, for the period 10/17/2011 through 11/28/2011.
3. Calculate an average, using STR_4, for the period 12/05/2011 through 02/01/2012.
4. Calculate an average of all observations for STR_7.
5. Calculate an average, using STR_8, for the period 10/17/2011 through 12/27/2011.
6. Calculate an average, using STR_8, for the period 01/03/2012 through 02/01/2012.

It would seem I'm not clever enough to come up with some method, or I'm unaware of a procedure that would allow me to do something like that.

Does anyone have any suggestions?

Thanks

3 REPLIES 3

## Re: Calculate multiple averages on same column

Use a data step view and create new variables.

``````   if '17OCT2011'd le date le '28NOV2011'd then do;
str_4_D1 = str_4;
end;``````
Super User

## Re: Calculate multiple averages on same column

One way is to calculate all combinations and then filter out what you don't want.

``````data have;
format Date mmddyy10.;
input Date STR_3 STR_4 STR_7 STR_8;

if  '17Oct2011'd < Date < '28Nov2011'd then group4=1;
else if '05Dec2011'd <Date <'01Feb2012'd then group4=2;

if  '17Oct2011'd < Date < '27Dec2011'd then group8=1;
else if '03Jan2012'd < Date < '01Feb2012'd then group8=2;

datalines;
18917 . 17386 . 6756
18924 . 20913 . 9101
18931 . 19672 . 8734
18938 . 20362 . 9175
18945 . 21325 . 8749
18952 . 21129 . 8932
18959 . 21540 . 8374
18966 17714 19860 . 9645
18973 14983 22248 . 9055
18981 12833 20781 . 8709
18988 17347 21402 . 8886
18995 17295 22218 4400 9738
19002 17337 21577 3813 8830
19009 17402 23090 5052 10119
19016 17395 23426 5001 9480
19024 14114 24574 4361 9702
;
run;

title;
proc means data=have mean missing stackods;
class group4 group8;
var str_3 str_7 str_4 str_7;
ods output summary=want;
run;

``````

Super User

## Re: Calculate multiple averages on same column

```

data have;
format Date mmddyy10.;
input Date STR_3 STR_4 STR_7 STR_8;
datalines;
18917 . 17386 . 6756
18924 . 20913 . 9101
18931 . 19672 . 8734
18938 . 20362 . 9175
18945 . 21325 . 8749
18952 . 21129 . 8932
18959 . 21540 . 8374
18966 17714 19860 . 9645
18973 14983 22248 . 9055
18981 12833 20781 . 8709
18988 17347 21402 . 8886
18995 17295 22218 4400 9738
19002 17337 21577 3813 8830
19009 17402 23090 5052 10119
19016 17395 23426 5001 9480
19024 14114 24574 4361 9702
;
run;
proc sql;
select
(select mean(STR_3) from have) as mean_STR_3,
(select mean(STR_4) from have where STR_3 is missing) as mean1_STR_4,
(select mean(STR_4) from have where STR_3 is not missing) as mean2_STR_4,
(select mean(STR_7) from have) as mean_STR_7,
(select mean(STR_8) from have where STR_7 is missing) as mean1_STR_8,
(select mean(STR_8) from have where STR_7 is not missing) as mean2_STR_8
from have(obs=1);
quit;

```
Discussion stats
• 3 replies
• 1066 views
• 4 likes
• 4 in conversation