SAS Coding for Average (Mean) among parts of column

[DavidLie]

Hi Everyone,

I am in my final stage of getting my dataset ready. I just need to do one last thing and I hope the SAS community can help me to do in the fastest way as I have approximately 1 million obsevation.

This is what I have:

Product	Week	Store 1	Store 2	Store 3
1	1	44	88	68
1	1	57	65	14
1	1	3	35	37
1	2	48	100	58
1	2	81	9	39
1	2	19	17	13

This data contains product (labelled 1, 2, 3, etc.); week (week identifier from 1 to 52); and store earning (store 1 to 276).

What do I want to achieve?

Product	Week	Store	Average
1	1	1	34.66667
1	1	2	62.66667
1	1	3	39.66667
1	2	1	49.33333
1	2	2	42
1	2	3	36.66667

I want to take the average earnings from each store by product and week. For example, the first row (i.e., Product 1, Week 1, Store 1 - the average is calculated based on the average of 44, 57 and 3, and that is 34.67).

Can anyone lend me a hand on this problem?

Many thanks.

David

[Ruslan]

Here you go!!!

data have;

input Product Week Store_1 Store_2 Store_3 ;

datalines;

1 1 44 88 68

1 1 57 65 14

1 1 3 35 37

1 2 48 100 58

1 2 81 9 39

1 2 19 17 13

;

%let vars = Store_1 Store_2 Store_3;

proc means data=have noprint;       

by Product Week;   

var &vars;

output out=want mean=;   

run;

[DavidLie]

Hi Ruslan,

Thank you for your email. greatly appreciated. I would like to ask further question - how do I specify the line

%let vars = store_1 ;

As currently I have 276 stores?

Is there a way to shortform that? Like using array {*}? Is that possible?

David

[Ruslan]

Well, the only way I can think about is to create the list of Store_1, Store_2 until Store_276 in excel and then put the entire list to %vars = Store_1 Store_2 Store_3 ... Store_276. let me know if this works for you

[RW9]

Hi,

I think your reponse to the suggestion shows exactly why your data is setup incorrectly.  I always recommend to have a normalised table, this is where the data goes down, rather than across.  It is far easier to do by group processing if the data is normalised. 

data have;
input Product Week Store_1 Store_2 Store_3 ;
datalines;
1 1 44 88 68
1 1 57 65 14
1 1 3 35 37
1 2 48 100 58
1 2 81 9 39
1 2 19 17 13
;
run;
data normalised (drop=store_:);
  set have;
  array store_{3};
  do store=1 to dim(store_);
    result=store_{store};
    output;
  end;
run;
proc means data=have noprint;       
  by Product Week store;   
  var result;
  output out=want mean=;   
run;

[Steelers_In_DC]

data table;
infile cards;
input Product$     Week     Store_1     Store_2     Store_3;
cards;
1     1     44     88     68
1     1     57     65     14
1     1     3     35     37
1     2     48     100 58
1     2     81     9     39
1     2     19     17     13
;

proc summary data=table;
var store:;
by product week;
output out=want(drop=_type_ _freq_) mean=;
run;