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
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;
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;
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;
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
Thanks guys as usual... all equally helpful..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.