Contributor
Posts: 21

# SAS Macros for determining Cross Price Effect

Hi everyone, I hope you can help me with my project. I am not an expert SAS user - been using it for just over 4 months. My dataset is similar to the attached Excel file but more complicated (with 100,000+ observation). 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 more than 20 products).

1 to 6: competition identifier. For example, the first observation contains the value of 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 shaded 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.

David Lie

Contributor
Posts: 66

## Re: SAS Macros for determining Cross Price Effect

[ Edited ]

(edited - separated into two replies for readability)

This solution should get you part of the way there. It uses a combination of data steps and PROC SQL.

If you have a very large number of store codes or store code numbers are not consecutive (i.e. 1, 2, 3, ..., etc.), then an approach that differs from this one would be needed.

``````DATA have;
ATTRIB Type length=\$10
Store_Code Week Product dummy1 dummy2 dummy3 dummy4 dummy5 dummy6 length=8
Price format=comma9.2;
INPUT Type \$ Store_Code Week Product dummy1 dummy2 dummy3 dummy4 dummy5 dummy6 Price;
DATALINES;
Grocery 1 1 1 0 1 0 1 1 0 6
Grocery 1 1 2 0 1 0 1 1 0 3
Grocery 1 1 3 0 1 0 1 1 0 3
Grocery 1 2 1 0 1 0 1 1 0 5
Grocery 1 2 2 0 1 0 1 1 0 7
Grocery 1 2 3 0 1 0 1 1 0 10
Grocery 1 3 1 0 1 0 1 1 0 7
Grocery 1 3 2 0 1 0 1 1 0 8
Grocery 1 3 3 0 1 0 1 1 0 10
Grocery 2 1 1 1 0 0 0 0 1 2
Grocery 2 1 2 1 0 0 0 0 1 6
Grocery 2 1 3 1 0 0 0 0 1 3
Grocery 2 2 1 1 0 0 0 0 1 6
Grocery 2 2 2 1 0 0 0 0 1 5
Grocery 2 2 3 1 0 0 0 0 1 3
Grocery 2 3 1 1 0 0 0 0 1 7
Grocery 2 3 2 1 0 0 0 0 1 5
Grocery 2 3 3 1 0 0 0 0 1 9
Convenient 3 1 1 0 0 0 1 1 1 2
Convenient 3 1 2 0 0 0 1 1 1 6
Convenient 3 1 3 0 0 0 1 1 1 5
Convenient 3 2 1 0 0 0 1 1 1 10
Convenient 3 2 2 0 0 0 1 1 1 10
Convenient 3 2 3 0 0 0 1 1 1 10
Convenient 3 3 1 0 0 0 1 1 1 2
Convenient 3 3 2 0 0 0 1 1 1 6
Convenient 3 3 3 0 0 0 1 1 1 7
Convenient 4 1 1 1 0 1 0 1 1 10
Convenient 4 1 2 1 0 1 0 1 1 4
Convenient 4 1 3 1 0 1 0 1 1 3
Convenient 4 2 1 1 0 1 0 1 1 2
Convenient 4 2 2 1 0 1 0 1 1 3
Convenient 4 2 3 1 0 1 0 1 1 4
Convenient 4 3 1 1 0 1 0 1 1 10
Convenient 4 3 2 1 0 1 0 1 1 4
Convenient 4 3 3 1 0 1 0 1 1 8
Mass_Merc 5 1 1 1 0 1 1 0 0 9
Mass_Merc 5 1 2 1 0 1 1 0 0 9
Mass_Merc 5 1 3 1 0 1 1 0 0 4
Mass_Merc 5 2 1 1 0 1 1 0 0 3
Mass_Merc 5 2 2 1 0 1 1 0 0 8
Mass_Merc 5 2 3 1 0 1 1 0 0 7
Mass_Merc 5 3 1 1 0 1 1 0 0 2
Mass_Merc 5 3 2 1 0 1 1 0 0 4
Mass_Merc 5 3 3 1 0 1 1 0 0 2
Mass_Merc 6 1 1 0 1 1 1 0 0 7
Mass_Merc 6 1 2 0 1 1 1 0 0 5
Mass_Merc 6 1 3 0 1 1 1 0 0 6
Mass_Merc 6 2 1 0 1 1 1 0 0 8
Mass_Merc 6 2 2 0 1 1 1 0 0 8
Mass_Merc 6 2 3 0 1 1 1 0 0 9
Mass_Merc 6 3 1 0 1 1 1 0 0 10
Mass_Merc 6 3 2 0 1 1 1 0 0 9
Mass_Merc 6 3 3 0 1 1 1 0 0 3
;
RUN;

/* change boolean values for each store to its corresponding
store #; this allows us to to perform a lookup in a later step */
DATA have_fixed;
SET have;
LENGTH sc1-sc6 8;
ARRAY orig_array dummy1-dummy6;
ARRAY new_array  sc1-sc6;

DO OVER orig_array;
i+1;

IF orig_array=0 THEN new_array=.;
ELSE new_array=orig_array*i;

IF i=6 THEN i=0; /* reset i to zero at the end of the row */
END;

RUN;

/* make a copy of the dataset to avoid using the same dataset in a lookup */
DATA have_fixed_copy(drop=i dummy1-dummy6 sc1-sc6);
SET have_fixed;
RUN;

/* use a macro variable to avoid typing the same fragment again and again */
%LET repetitive_clause  = %STR(BB.Store_Code = AA.sc1    OR BB.Store_Code = AA.sc2
OR BB.Store_Code = AA.sc3 OR BB.Store_Code = AA.sc4
OR BB.Store_Code = AA.sc5 OR BB.Store_Code = AA.sc6);
PROC SQL;
CREATE TABLE want AS
SELECT Type, Store_Code, Week, Product
, dummy1, dummy2, dummy3, dummy4, dummy5, dummy6
, sc1, sc2, sc3, sc4, sc5, sc6, Price
, (SELECT AVG(Price) FROM have_fixed_copy AS BB
WHERE BB.Type = 'Grocery'
AND BB.Week = AA.Week
AND BB.Product = AA.Product
AND (&repetitive_clause)
AND BB.Store_Code <> AA.Store_Code) AS Price_Grocer
, (SELECT AVG(Price) FROM have_fixed_copy AS BB
WHERE BB.Type = 'Convenient'
AND BB.Week = AA.Week
AND BB.Product = AA.Product
AND (&repetitive_clause)
AND BB.Store_Code <> AA.Store_Code) AS Price_Conv
, (SELECT AVG(Price) FROM have_fixed_copy AS BB
WHERE BB.Type = 'Mass_Merc'
AND BB.Week = AA.Week
AND BB.Product = AA.Product
AND (&repetitive_clause)
AND BB.Store_Code <> AA.Store_Code) AS Price_Mass_Merc
FROM have_fixed AS AA;
QUIT;``````

Here is the output of the above code:

Contributor
Posts: 66

## Re: SAS Macros for determining Cross Price Effect

(edited - separated into two replies for readability) - this is a follow-up to first reply

If you have a whole bunch of "store code" variables, you may want to take the macro approach to avoid lots of typing. One portion of your macro might involve calling PROC CONTENTS or PROC SQL to generate a list of store code variables dynamically.

Since the list of variables are themselves in datasets, you could, for instance:

• create a macro variable, that contains a delimited list of store code variable names (using for example "PROC SQL; select name into :my_long varlist SEPARATED BY ' ' FROM varlist WHERE name LIKE 'sc%' " ... etc., etc. statement)
• or you might be able to use a "DATA _NULL_" statement on the dataset varlist to generate a ".sas" program (or portion of a program) that contains IF/THEN/ELSE logic.

``````/* generate a list of store variables */
PROC CONTENTS DATA=have_fixed
OUT=varlist(keep=libname memname name type length
varnum label format formatl formatd);
RUN;

PROC SORT DATA=varlist(WHERE=(lowcase(name) LIKE 'sc%')); BY varnum; RUN;

/* same as above, but uses PROC SQL */
PROC SQL;
CREATE TABLE varlist_sql AS
SELECT libname, memname, name, type, length, npos, varnum, label, format
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = 'WORK'
AND MEMNAME = 'HAVE_FIXED'
AND lowcase(name) LIKE 'sc%';
QUIT;``````

Discussion stats
• 2 replies
• 313 views
• 0 likes
• 2 in conversation