How to remove observations with no change in the dependent variable in a regression ?

[Omar Hatteea]

I have a panel data set that is of the form

shpro	date	price
1	1	100
1	2	100
1	3	100
1	4	100
2	1	98
2	2	100
2	3	102
2	4	104
3	1	99
3	2	100
etc.	etc.	etc.

where shpro is a variable that represents the same product within the same shop ie it is product-shop specific. The date represents the date of the price reading and price is price of the product in that shop.

I am carrying out a fixed effects regression with time fixed effects and shop-product fixed effects. I wish to condition my regression on the fact that the price of each product at date=1 is different to the price of that same product at date=4. I initially generated a variable for price at date=1 and date=4 but of course this does not work since each observation has only one date. I have an inkling that I may need to reshape the data, but I am not entirely sure on how to do this.

Any help will be so much appreciated.

[Joro Kolev]

The code

. sort shpro date

. by shpro: drop if price[1]==price[_N]
(4 observations deleted)

will drop the observations for which price is constant withing the panel identifier.

[Omar Hatteea]

Ah sorry, I've slightly mis-displayed the data. I have shown the data for dates 1 to 4, however my data gives data from dates 1-100. How would I only drop the observations where the price in date 1 is the same as the price in date 4 given there are dates up to 100?

Thanks again!!!!

[Joro Kolev]

Try

 
 . sort shpro date  . by shpro: drop if price[1]==price[4]

[Omar Hatteea]

Unfortunately, that seems to delete all my observations even though there are values of shpro where the price in period 1 is not the same as the price in period 4....

[Joro Kolev]

Type -. findit dataex-, install -dataex- and post a sample of your data.

Probably I do not understand what you want to do, but I do not see any problem with the suggestion I gave you above. It is supposed to verify whether price at date 1 is same as price at date 4, and then if this is so, drop all the observations for the corresponding panel identifier.

[Omar Hatteea]

Sorry I am unsure how to extract the data as requested but here is a sample of my data

price date shpro
1449 18280 23
1449 18281 23
1449 18282 23
1449 18283 23
1449 18284 23
1449 18285 23
1449 18286 23
1449 18287 23
1449 18288 23
1449 18289 23
1449 18290 23
1449 18291 23
1449 18296 23
1449 18297 23
1449 18298 23
1449 18299 23
1449 18300 23
1449 18301 23
1449 18302 23
1595 18303 23
1595 18304 23
1595 18305 23
1595 18306 23
1595 18307 23
1595 18308 23
1595 18309 23
1595 18310 23
1595 18311 23
1595 18312 23
1595 18313 23
1595 18314 23
1595 18315 23
1595 18316 23
1595 18317 23
1595 18318 23
1595 18319 23
1327 18273 24
1327 18274 24
1319 18275 24
1309 18276 24
1309 18277 24
1309 18278 24
1309 18279 24
1309 18280 24
1309 18281 24
1309 18282 24
1309 18283 24
1309 18284 24
1309 18285 24
1309 18286 24
1309 18287 24
1309 18288 24
1309 18289 24
1309 18290 24
1309 18291 24
1315 18296 24
1315 18297 24
1315 18298 24
1315 18299 24
1315 18300 24
1315 18301 24
1315 18302 24
1455 18303 24
1455 18304 24
1455 18305 24
1455 18306 24
1455 18307 24
1455 18308 24
1455 18309 24
1455 18310 24
1455 18311 24
1455 18312 24
1455 18313 24
1455 18314 24
1455 18315 24
1455 18316 24
1455 18317 24
1455 18318 24
1479 18319 24

The dates of interest are 18301 and 18310, which for shpro=23 and shpro=24, they are different. However the code above:

sort shpro date
by shpro: drop if price[18301] == price[18309]

Results are:

. by shpro: drop if price[18301]==price[18309]
(928,066 observations deleted)

Which deletes all my observations. Apologies if I'm mistaking you but it doesn't seem to work.

[Carlo Lazzaro]

Omar:
I do hope that what follows can be helpful:

sort shpro date
bysort shpro: gen flag=1 if price[1]==price[4]
bysort shpro: g counter=_n
drop if counter==1 & flag==1
drop if counter==4 & flag==1

[Omar Hatteea]

Dear Carlo,

I do not know why, but when I carry out

 
 bysort shpro: gen flag=1 if price[1]==price[4]

for my data, the flag is generated ==1 for all observations even though there are clearly observations such as shpro=23 and shpro=24 as above where this is not the case. I have replaced the arbitrary numbers with my specified values of 18301 and 18309 in case you thought this was the issue.

I really really appreciate your help, but I am not sure why it isn't working.

[Nick Cox]

You should try what Joro and Carlo are suggesting. You should not guess that they mean or imply something else. In your case no panel contains 18000 or so observations so the values for observation 18301 and 18309 are both returned as missing, hence equal to each other. The subscript is not the date, but the observation number — under by: the observation number within each panel,

[Carlo Lazzaro]

Obviously grateful to Nick for his appreciated endorsement, that's what I eventually got running the code suggested in #8:

. input price date shpro

         price       date      shpro
  1.  1449 18280 23
  2.  1449 18281 23
  3.  1449 18282 23
  4.  1449 18283 23
  5.  1449 18284 23
  6.  1449 18285 23
  7.  1449 18286 23
  8.  1449 18287 23
  9.  1449 18288 23
 10.  1449 18289 23
 11.  1449 18290 23
 12.  1449 18291 23
 13.  1449 18296 23
 14.  1449 18297 23
 15.  1449 18298 23
 16.  1449 18299 23
 17.  1449 18300 23
 18.  1449 18301 23
 19.  1449 18302 23
 20.  1595 18303 23
 21.  1595 18304 23
 22.  1595 18305 23
 23.  1595 18306 23
 24.  1595 18307 23
 25.  1595 18308 23
 26.  1595 18309 23
 27.  1595 18310 23
 28.  1595 18311 23
 29.  1595 18312 23
 30.  1595 18313 23
 31.  1595 18314 23
 32.  1595 18315 23
 33.  1595 18316 23
 34.  1595 18317 23
 35.  1595 18318 23
 36.  1595 18319 23
 37.  1327 18273 24
 38.  1327 18274 24
 39.  1319 18275 24
 40.  1309 18276 24
 41.  1309 18277 24
 42.  1309 18278 24
 43.  1309 18279 24
 44.  1309 18280 24
 45.  1309 18281 24
 46.  1309 18282 24
 47.  1309 18283 24
 48.  1309 18284 24
 49.  1309 18285 24
 50.  1309 18286 24
 51.  1309 18287 24
 52.  1309 18288 24
 53.  1309 18289 24
 54.  1309 18290 24
 55.  1309 18291 24
 56.  1315 18296 24
 57.  1315 18297 24
 58.  1315 18298 24
 59.  1315 18299 24
 60.  1315 18300 24
 61.  1315 18301 24
 62.  1315 18302 24
 63.  1455 18303 24
 64.  1455 18304 24
 65.  1455 18305 24
 66.  1455 18306 24
 67.  1455 18307 24
 68.  1455 18308 24
 69.  1455 18309 24
 70.  1455 18310 24
 71.  1455 18311 24
 72.  1455 18312 24
 73.  1455 18313 24
 74.  1455 18314 24
 75.  1455 18315 24
 76.  1455 18316 24
 77.  1455 18317 24
 78.  1455 18318 24
 79.  1479 18319 24
 80. end

. format date %td

. sort shpro date

. bysort shpro: gen flag=1 if price[1]==price[4]

. bysort shpro: g counter=_n

. drop if counter==1 & flag==1

. drop if counter==4 & flag==1

. drop flag

. list if shpro==23

     +---------------------------------+
     | price    date   shpro   counter |
     |---------------------------------|
  1. |  1449   18281      23         2 |
  2. |  1449   18282      23         3 |
  3. |  1449   18284      23         5 |
  4. |  1449   18285      23         6 |
  5. |  1449   18286      23         7 |
     |---------------------------------|
  6. |  1449   18287      23         8 |
  7. |  1449   18288      23         9 |
  8. |  1449   18289      23        10 |
  9. |  1449   18290      23        11 |
 10. |  1449   18291      23        12 |
     |---------------------------------|
 11. |  1449   18296      23        13 |
 12. |  1449   18297      23        14 |
 13. |  1449   18298      23        15 |
 14. |  1449   18299      23        16 |
 15. |  1449   18300      23        17 |
     |---------------------------------|
 16. |  1449   18301      23        18 |
 17. |  1449   18302      23        19 |
 18. |  1595   18303      23        20 |
 19. |  1595   18304      23        21 |
 20. |  1595   18305      23        22 |
     |---------------------------------|
 21. |  1595   18306      23        23 |
 22. |  1595   18307      23        24 |
 23. |  1595   18308      23        25 |
 24. |  1595   18309      23        26 |
 25. |  1595   18310      23        27 |
     |---------------------------------|
 26. |  1595   18311      23        28 |
 27. |  1595   18312      23        29 |
 28. |  1595   18313      23        30 |
 29. |  1595   18314      23        31 |
 30. |  1595   18315      23        32 |
     |---------------------------------|
 31. |  1595   18316      23        33 |
 32. |  1595   18317      23        34 |
 33. |  1595   18318      23        35 |
 34. |  1595   18319      23        36 |
     +---------------------------------+

. list if shpro==24

     +---------------------------------+
     | price    date   shpro   counter |
     |---------------------------------|
 35. |  1327   18273      24         1 |
 36. |  1327   18274      24         2 |
 37. |  1319   18275      24         3 |
 38. |  1309   18276      24         4 |
 39. |  1309   18277      24         5 |
     |---------------------------------|
 40. |  1309   18278      24         6 |
 41. |  1309   18279      24         7 |
 42. |  1309   18280      24         8 |
 43. |  1309   18281      24         9 |
 44. |  1309   18282      24        10 |
     |---------------------------------|
 45. |  1309   18283      24        11 |
 46. |  1309   18284      24        12 |
 47. |  1309   18285      24        13 |
 48. |  1309   18286      24        14 |
 49. |  1309   18287      24        15 |
     |---------------------------------|
 50. |  1309   18288      24        16 |
 51. |  1309   18289      24        17 |
 52. |  1309   18290      24        18 |
 53. |  1309   18291      24        19 |
 54. |  1315   18296      24        20 |
     |---------------------------------|
 55. |  1315   18297      24        21 |
 56. |  1315   18298      24        22 |
 57. |  1315   18299      24        23 |
 58. |  1315   18300      24        24 |
 59. |  1315   18301      24        25 |
     |---------------------------------|
 60. |  1315   18302      24        26 |
 61. |  1455   18303      24        27 |
 62. |  1455   18304      24        28 |
 63. |  1455   18305      24        29 |
 64. |  1455   18306      24        30 |
     |---------------------------------|
 65. |  1455   18307      24        31 |
 66. |  1455   18308      24        32 |
 67. |  1455   18309      24        33 |
 68. |  1455   18310      24        34 |
 69. |  1455   18311      24        35 |
     |---------------------------------|
 70. |  1455   18312      24        36 |
 71. |  1455   18313      24        37 |
 72. |  1455   18314      24        38 |
 73. |  1455   18315      24        39 |
 74. |  1455   18316      24        40 |
     |---------------------------------|
 75. |  1455   18317      24        41 |
 76. |  1455   18318      24        42 |
 77. |  1479   18319      24        43 |
     +---------------------------------+

. drop counter

[Omar Hatteea]

Sorry, my sincere apologies- I had wrongly assumed!

Just a couple of clarifications:

Since my data starts at different dates i.e. shpro 23 starts at 18281 and shpro 24 starts at 18273, and I want to eliminate observations when prices at date 18301 and 18309 are the same- does this still work?

Secondly, is there a way to eliminate all the observations for the observations that display the same prices at these dates i.e.e not just to remove the observations at date 18301 and 18309?

Thanks so much again and apologies for my misunderstanding

[Joro Kolev]

Omar, the solutions that both I and Carlo proposed to you assumed the following structure of the problem: Your data is identified by shpro , and then within shpro you have natural ordering by date, and then you want to drop observations when the price at first date is equal to price at 4th date.
1 My proposal drops all observations for given shpro when the above is true, that is when price at first observed occasion is equal to the price at fourth observed occasion.
2 Carlo's proposal differs in that it drops only the particular observations 1 and 4 in the ordering by date.


Given the new information regarding what you want to do that you are supplying, I think this is one possible solution:

. sort shpro

. by shpro: egen priceat18301 = mean(price) if date==18301
(77 missing values generated)

. by shpro: egen priceat18309 = mean(price) if date==18309
(77 missing values generated)

. by shpro: egen meanpriceat18301 = mean(priceat18301)

. by shpro: egen meanpriceat18309 = mean( priceat18309)

. drop if meanpriceat18301== meanpriceat18309 & !missing(meanpriceat18301,meanpriceat18309)
(0 observations deleted)

[Omar Hatteea]

This works perfectly!!! I'm so sorry I mis-represented the problem- THANK YOU SO MUCH FOR YOUR HELP - it is really appreciated.

[Joro Kolev]

I am glad that it worked, Omar ! The important thing is that we understood each other at the end