Issue with Replacing - (alternative) solution?

[Mike Warn]

Hey guys,

I am rather new to Stata and it seems like I am currently discovering my boundaries. I'd be very happy if someone is able to help me!


So i do have Panel Data - my time variable is MONTH (for example: 2000m7). ID is my identifier.

There is a variable called PRICE. I want to replace observations that have a missing value (that's what a " . " is called - right?) with the last available observation.

In principle I'd use:

bys ID (MONTH): replace PRICE = PRICE[_n-1] if PRC == .

However, there is a slight problem: I only want to use the last observation if it is not older than 6 months (if the last observation is older than 6 months I'd like to keep a missing value). Moreover, there might be gaps - so I guess using _n is not a good idea for an optimal solution.



Thank you in advance!

Best regards
Mike

[Nick Cox]

 
 bys ID (MONTH): replace PRICE = PRICE[_n-1] if missing(PRICE) & (MONTH - MONTH[_n-1]) < 6

[Nick Cox]

A twist on the above code is something like this -- otherwise a cascade of replacements might extend over longer periods.

clear
set obs 24 
gen id = 1 + (_n > 12)
bysort id : gen month = _n

gen price = 42 if inlist(_n, 4, 13)

bysort id (month): gen lastknown = month if price < . 
by id : replace lastknown = lastknown[_n-1] if missing(price)

by id : replace price = price[_n-1] if missing(price) & (month - lastknown) <= 6 

list, sepby(id)

     +-------------------------------+
     | id   month   price   lastkn~n |
     |-------------------------------|
  1. |  1       1       .          . |
  2. |  1       2       .          . |
  3. |  1       3       .          . |
  4. |  1       4      42          4 |
  5. |  1       5      42          4 |
  6. |  1       6      42          4 |
  7. |  1       7      42          4 |
  8. |  1       8      42          4 |
  9. |  1       9      42          4 |
 10. |  1      10      42          4 |
 11. |  1      11       .          4 |
 12. |  1      12       .          4 |
     |-------------------------------|
 13. |  2       1      42          1 |
 14. |  2       2      42          1 |
 15. |  2       3      42          1 |
 16. |  2       4      42          1 |
 17. |  2       5      42          1 |
 18. |  2       6      42          1 |
 19. |  2       7      42          1 |
 20. |  2       8       .          1 |
 21. |  2       9       .          1 |
 22. |  2      10       .          1 |
 23. |  2      11       .          1 |
 24. |  2      12       .          1 |
     +-------------------------------+