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
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;
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
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.