SAS macros for Cross Price

[DavidLie]

Hi everyone, I hope you can help me with my project. My dataset is similar to the attached Excel file but more complicated. This dataset contains different prices at store level in a particular week. More about the dataset:

Type: whether it is Grocery/ Convenient Store/ Mass Merchandiser

Store Code: store identifier

Week: define which week (i.e., in my dataset it contains from week 1 to 52 but in this example, we just keep it to 3)

Product: products that are sold in the store, in which we label them as product 1 to 3 (in my dataset, there are 20 products).

1 to 6: competition identifier. For example, the first observation has 1 on the column labelled '2' which means Store 1 is in competition with Store 2. Also, it has a value of 1 in column labelled '4' and '5' which means Store 1 is also in competition with Store 4 and 5.

Price: The price of that particular product.

What do I want to achieve?

Basically the columns that are labelled in yellow. I need to construct these three variables, which are "price of other groceries", "price of convenient stores" and "price of mass merchandiser". For example, the first observation is in direct competition with 2 (which is the grocery), 4 (which is the convenient store) and 5 (which is the mass merchandiser). Hence, the "price of other groceries" has to be the exact price for the price of Store 2 for product 1 in week 1 which is 2 (same product and week number). Similarly, "price of convenient stores" has to be 10 (from Store 4; product 1; week 1) and "price of mass merchandiser" is 9 due to the same logic.

If we look at Store 3 for product 1 and week 1, the competition for the grocery is in both Store 5 and 6. In order to calculate this, we will need to average the Store 5; Product 1; Week 1 (which is 9) and Store 6; Product 1; Week 1 (which is 7). Therefore, 8 should be the answer.

Does anyone can provide me with the macros for this? My current strategy is to actually calculate by each store as the competition is exactly similar. But in case someone can generate much better and more efficient macros, that will be greatly appreciated.

[RW9]

Hi,

I am afraid for me that sounds to much like you want someone to write the code for you rather than a specific question.  What I would suggest is you sit down and write the SAS Base code for one example.   Once you have that working, then think about maybe putting it into a macro, however I would suggest you'd be just as well off with retain, aggregates or arrays.  If you have a specific question, provide a small datastep with some test data, and required output.

Note that this is the same post as here: 

https://communities.sas.com/t5/General-SAS-Programming/SAS-Macros-for-determining-Cross-Price-Effect...