include all average in the set

[podarum]

Hi,  I have a dataset with 3 dates/Years by each store and some metrics, and I'd like to get an average (for all 3 dates/Years) by each store, but also an avergae by all stores.

Here's what I mean

HAVE:        

SYORE  YEAR  Metric1  Metric2

Store1     2001     2            5

Store1     2002     3            6

Store1     2003     4            7

Store2     2001     5            1

Store2     2002     6            1

Store2     2003     7            1

Store3     2001     7            3

Store3     2002     9            4

Store3     2003    11           5

WANT: (average for each store and one for all)

               Metric1     Metric2

Store1        3               6

Store2        6               1

Store3        9               4

ALL            6             3.66

Thanks

[art297]

One way would be to use proc summary:

proc summary data=have;

  var me:;

  class store;

  output out=want (drop=_:) mean=;

run;

data want;

  set want;

  if missing(store) then store='All';

run;

[ArtC]

If you want the result in a SAS data set then SUMMARY does the trick.  If you want a table consider TABULATE or REPORT.

data have;
input STORE $ YEAR  Metric1  Metric2;
datalines;
Store1     2001     2            5
Store1     2002     3            6
Store1     2003     4            7
Store2     2001     5            1
Store2     2002     6            1
Store2     2003     7            1
Store3     2001     7            3
Store3     2002     9            4
Store3     2003    11           5
run;

proc tabulate data=have;
class store;
var metric1 metric2;
table store all, (metric:*mean);
run;

proc report data=have nowd;
column store metric1 metric2;
define store /group;
define metric:/mean;
rbreak after /summarize;
run;

[art297]

If you only want a printout, and it doesn't have to look exactly like your example, you could also use:

proc means data=have mean printalltypes;

  var me:;

  class store;

run;

[Haikuo]

If to choose Proc SQL,  you could try:

data have;

input STORE $ YEAR  Metric1  Metric2;

datalines;

Store1     2001     2            5

Store1     2002     3            6

Store1     2003     4            7

Store2     2001     5            1

Store2     2002     6            1

Store2     2003     7            1

Store3     2001     7            3

Store3     2002     9            4

Store3     2003    11           5

run;

proc sql;

create table want as

select store, mean(metric1) as metric1, mean(metric2) as metric2 from have

group by store

union

select 'all' as store, mean(metric1) as metric1, mean(metric2) as metric2 from have

;

quit;

proc print;run;

And of course, there alway will be a data step solution:

data want (keep=store m1 m2 rename=(m1=metric1 m2=metric2));

  do _n_=1 by 1 until (last.store);

set have end=last nobs=nobs;

  by store;

      _m1+metric1;

      _m2+metric2;

  end;

       m1=_m1/_n_;

       m2=_m2/_n_;

__m1+_m1;

__m2+_m2;

      output;

      call missing (of _m1 _m2);

if last    then do;

    store='all';

    m1=__m1/nobs;

    m2=__m2/nobs;

    output;

end;

run;

Regards,

Haikuo

[podarum]

Thanks guys as usual... all equally helpful..