BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

5 REPLIES 5
art297
Opal | Level 21

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
Rhodochrosite | Level 12

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
Opal | Level 21

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
Onyx | Level 15

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
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 728 views
  • 0 likes
  • 4 in conversation