BookmarkSubscribeRSS Feed
DavidLie
Obsidian | Level 7

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

5 REPLIES 5
Ruslan
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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