DATA Step, Macro, Functions and more

Calculate multiple averages on same column

Reply
Occasional Contributor
Posts: 12

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:

Capture.PNG

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

Respected Advisor
Posts: 3,799

Re: Calculate multiple averages on same column

Posted in reply to KyleM_Corrie

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
Posts: 19,817

Re: Calculate multiple averages on same column

Posted in reply to KyleM_Corrie

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
Posts: 10,035

Re: Calculate multiple averages on same column

Posted in reply to KyleM_Corrie


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;

Ask a Question
Discussion stats
  • 3 replies
  • 269 views
  • 4 likes
  • 4 in conversation