topic Re: SAS Coding for Average (Mean) among parts of column in SAS Programming

SAS Coding for Average (Mean) among parts of column

DavidLie — Wed, 07 Oct 2015 10:45:14 GMT

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

Re: SAS Coding for Average (Mean) among parts of column

Ruslan — Wed, 07 Oct 2015 11:00:49 GMT

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;

Re: SAS Coding for Average (Mean) among parts of column

DavidLie — Wed, 07 Oct 2015 12:18:30 GMT

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

Re: SAS Coding for Average (Mean) among parts of column

Ruslan — Wed, 07 Oct 2015 12:25:53 GMT

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

Re: SAS Coding for Average (Mean) among parts of column

RW9 — Wed, 07 Oct 2015 12:46:06 GMT

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;

Re: SAS Coding for Average (Mean) among parts of column

Steelers_In_DC — Wed, 07 Oct 2015 13:48:19 GMT

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;