SAS Macros for determining Cross Price Effect

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

 

Thanks in advance

 

David Lie

Contributor hbi
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: 

 

cross_price_effect_sas_eg.gif

 

Contributor hbi
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;

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 278 views
  • 0 likes
  • 2 in conversation