BookmarkSubscribeRSS Feed
DavidLie
Obsidian | Level 7

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.

1 REPLY 1
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 691 views
  • 1 like
  • 2 in conversation