topic include all average in the set in SAS Procedures

include all average in the set

podarum — Sun, 12 Feb 2012 18:00:29 GMT

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

include all average in the set

art297 — Sun, 12 Feb 2012 18:49:30 GMT

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;

include all average in the set

ArtC — Sun, 12 Feb 2012 22:41:30 GMT

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;

include all average in the set

art297 — Mon, 13 Feb 2012 00:29:20 GMT

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;

Re: include all average in the set

Haikuo — Mon, 13 Feb 2012 00:55:20 GMT

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

include all average in the set

podarum — Mon, 13 Feb 2012 03:04:48 GMT

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