Create indicator variables to tag previous observations (panel data)

[Matthias Suchanek]

I want to examine the relationship between prices and the supply of gasoline. My dataset consists of 1.2 million observations and contains quoted gas prices from all gas Stations in France throughout the year of 2016.

My assumption is that prices are set siginificantly higher if the supply of gasoline is low (close to being sold out). However, I do not have any data on the amount of gasoline that is left. My only indicator is the quoted price of gasoline which is set to -1 when gasoline is sold out completely. Now I would like to focus on the previous observations before the gasoline is sold out.
For every observation where price is equal to -1,

I would like to define a range of 3 previous observations to show that for these observations the supply of gasoline is low already. Perhaps by creating variables indicating the supply/scarcity of gasoline. In turn, I want to regress price

on

the new indicator variable.
As I am new to Stata I am not sure how to translate this into code.

to clarify the meaning of the variables in the example:

id

= the price adjustment ID

stid

= unique ID of gas Station

price

= price of gasoline*1000, or -1 when sold out

modification

= time when price was modified/quoted

Thank you very much in advance

Best Regards

Matthias

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(id stid) double(price modification)
29772366 27600001 1107 1779354305000
29772367 27600001 1112 1779638724000
29772368 27600001 1116 1.7797863e+12
29772369 27600001 1140 1.7798727e+12
29772663 27600001   -1 1.7800455e+12
end
format %tc modification

[Clyde Schechter]

Try this:

by stid (modification), sort: gen byte scarce = inlist(-1, price[_n+1], ///
    price[_n+2], price[_n+3])

By the way, I think you are going to get yourself in trouble using -1 as a code for no price because sold out. At some point you are likely to forget about this little anomaly and do some sort of calculations with the price variable and the -1 value will be treated as a real numeric -1 producing nonsense results. You would be better off replacing those -1's with a missing value. If you need to distinguish missing value because gas is sold out from other circumstances where no price information is available, use one of Stata's extended missing values. (Read -help missing- if you are not familiar with Stata's system missing and extended missing values.)

Thank you for using -dataex- on your very first post and for asking a clear question.

[Matthias Suchanek]

Dear Clyde,
This works perfectly. Thank You! And yes, it's a good idea to replace those -1's with a missing value.