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..
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.