Contributor
Posts: 21

# SAS Coding for Average (Mean) among parts of column

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

Frequent Contributor
Posts: 75

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

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;

Contributor
Posts: 21

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

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

Frequent Contributor
Posts: 75

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

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

Super User
Posts: 9,599

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

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;```
Valued Guide
Posts: 864

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

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;

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