Expanding backwards

[Coen Menken]

Hello,

Im working on a dataset and want to expand my dataset backwards for 2 months extra. The dataex I added shows first the GVKEY (company ID), then the date and then the variabele (DLCQ).

For example: if the data is june 2009 I want to add May and April to the date variabele and take over the value of DLCQ in these months (25.353 in this case)

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey long date double dlcq
"001004" 14669 25.353
"001004" 14761 26.314
"001004" 14853 48.151
"001004" 14944 50.437
"001004" 15034 49.665
"001004" 15126 13.652
"001004" 15218 65.396
"001004" 15309 71.162
"001004" 15399 20.89

Thank you!

[Nick Cox]

Thanks for the dataex example, although it's truncated and you should also copy both the CODE delimiters for readability.

You seem to have monthly data with gaps. If so, you are much better off with monthly dates. If you xtset or tsset properly you can then fill in your gaps.

. clear

. input str6 gvkey long date double dlcq

         gvkey          date        dlcq
  1. "001004" 14669 25.353
  2. "001004" 14761 26.314
  3. "001004" 14853 48.151
  4. "001004" 14944 50.437
  5. "001004" 15034 49.665
  6. "001004" 15126 13.652
  7. "001004" 15218 65.396
  8. "001004" 15309 71.162
  9. "001004" 15399 20.89
 10. end
.
. format date %td

. list

     +-----------------------------+
     |  gvkey        date     dlcq |
     |-----------------------------|
  1. | 001004   29feb2000   25.353 |
  2. | 001004   31may2000   26.314 |
  3. | 001004   31aug2000   48.151 |
  4. | 001004   30nov2000   50.437 |
  5. | 001004   28feb2001   49.665 |
     |-----------------------------|
  6. | 001004   31may2001   13.652 |
  7. | 001004   31aug2001   65.396 |
  8. | 001004   30nov2001   71.162 |
  9. | 001004   28feb2002    20.89 |
     +-----------------------------+

. gen mdate = mofd(date)

. encode gvkey, gen(id)

. xtset id mdate
       panel variable:  id (strongly balanced)
        time variable:  mdate, 481 to 505, but with gaps
                delta:  1 unit

. l

     +----------------------------------------------+
     |  gvkey        date     dlcq   mdate       id |
     |----------------------------------------------|
  1. | 001004   29feb2000   25.353     481   001004 |
  2. | 001004   31may2000   26.314     484   001004 |
  3. | 001004   31aug2000   48.151     487   001004 |
  4. | 001004   30nov2000   50.437     490   001004 |
  5. | 001004   28feb2001   49.665     493   001004 |
     |----------------------------------------------|
  6. | 001004   31may2001   13.652     496   001004 |
  7. | 001004   31aug2001   65.396     499   001004 |
  8. | 001004   30nov2001   71.162     502   001004 |
  9. | 001004   28feb2002    20.89     505   001004 |
     +----------------------------------------------+

. tsfill

. list, sep(3)

     +----------------------------------------------+
     |  gvkey        date     dlcq   mdate       id |
     |----------------------------------------------|
  1. | 001004   29feb2000   25.353     481   001004 |
  2. |                  .        .     482   001004 |
  3. |                  .        .     483   001004 |
     |----------------------------------------------|
  4. | 001004   31may2000   26.314     484   001004 |
  5. |                  .        .     485   001004 |
  6. |                  .        .     486   001004 |
     |----------------------------------------------|
  7. | 001004   31aug2000   48.151     487   001004 |
  8. |                  .        .     488   001004 |
  9. |                  .        .     489   001004 |
     |----------------------------------------------|
 10. | 001004   30nov2000   50.437     490   001004 |
 11. |                  .        .     491   001004 |
 12. |                  .        .     492   001004 |
     |----------------------------------------------|
 13. | 001004   28feb2001   49.665     493   001004 |
 14. |                  .        .     494   001004 |
 15. |                  .        .     495   001004 |
     |----------------------------------------------|
 16. | 001004   31may2001   13.652     496   001004 |
 17. |                  .        .     497   001004 |
 18. |                  .        .     498   001004 |
     |----------------------------------------------|
 19. | 001004   31aug2001   65.396     499   001004 |
 20. |                  .        .     500   001004 |
 21. |                  .        .     501   001004 |
     |----------------------------------------------|
 22. | 001004   30nov2001   71.162     502   001004 |
 23. |                  .        .     503   001004 |
 24. |                  .        .     504   001004 |
     |----------------------------------------------|
 25. | 001004   28feb2002    20.89     505   001004 |
     +----------------------------------------------+

. format mdate %tm

Copying forward is an FAQ. https://www.stata.com/support/faqs/d...issing-values/ Consider also interpolation.

[Coen Menken]

Hi Nick,

Thanks for the feedback on the dataex part I will take it into account writing my next post (and hope I do it properly this time). I still have a question regarding the topic. If I use the following code:

gen dlc = dlcq
bysort id: carryforward dlc, gen(dlc2)

I get that the value of for example 29 February is carry forward to the next 2 months. Is it possible to do this backwards so that I get the DLCQ value of 31 May on the gap values between 29 February and 31 May?

Thank you!

[Nick Cox]

carryforward is from SSC, as you are asked to explain. I've never used it.

More crucially, sorry, but I can't understand what you're doing without a data example. You seem to be hoping that we can imagine data and results that you aren't showing us.

The link given in #2 explains about copying backward in time. Just copying backwards and forwards seems a dubious procedure for this kind of data, but perhaps it's standard.

My advice in #2 was strongly to think in terms of monthly dates, not daily dates.