topic Re: Calculate multiple averages on same column in SAS Programming

Calculate multiple averages on same column

KyleM_Corrie — Thu, 31 Mar 2016 16:02:09 GMT

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:

Calculate an average of all observations for STR_3.
Calculate an average, using STR_4, for the period 10/17/2011 through 11/28/2011.
Calculate an average, using STR_4, for the period 12/05/2011 through 02/01/2012.
Calculate an average of all observations for STR_7.
Calculate an average, using STR_8, for the period 10/17/2011 through 12/27/2011.
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

Re: Calculate multiple averages on same column

data_null__ — Thu, 31 Mar 2016 16:20:36 GMT

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;

Re: Calculate multiple averages on same column

Reeza — Thu, 31 Mar 2016 16:24:00 GMT

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;

Re: Calculate multiple averages on same column

Ksharp — Fri, 01 Apr 2016 02:34:55 GMT



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;