Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filling missing values with existing ones if they have the same date

    Hi,
    I have a time series of portfolio returns. I would like to calculate a long-short stock portfolio where I deduct the returns of portfolio 1 from portfolio 10. I used the values of "pfrt" to create 2 separate variables "pf1rt" and "pf10rt" which include the returns of each portfolio. Now I have 2 additional columns with return data by date. Unfortunately, these columns have missing values in the rows of other portfolios e.g. no values of "pf1rt" in the rows of portfolio 2-10. How can I replace the missing values with existing ones by matching dates?

    datadate = Date
    pf = Name of the portfolio (1-10)
    pfrt = Portfolio return
    pf1rt = Return of portfolio 1
    pf10rt = Return of portfolio 10

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input long datadate float(pf pfrt pf1rt pf10rt)
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    20850 1 .8388038 .8388038 .
    end
    format %d datadate
    Any help would be much appreciated.

    Kind regards,
    Alex
    Last edited by Alexander Schmidt; 14 Jun 2018, 06:49.

  • #2
    You didn't get a quick answer. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex. Thank you for providing data.

    I can't understand your data - you seem to have a long list of exactly the same values. If you have the right value in the same observation as what you want to replace, something like:
    g newpf10rt=pf10rt
    replace newpf10rt=pf1rt if newpf10rt==.

    Would work.

    Comment


    • #3
      Thank you for your answer Phil. I try to break it down more simple. I sort my data by date. It would look like this:
      Code:
      datadate pf1rt
      ...
      31dec2017 .838
      31dec2017 .838
      31dec2017 .
      31dec2017 .838
      31dec2017 .838
      31dec2017 .
      30nov2017 .909
      30nov2017 .909
      30nov2017 .
      30nov2017 .909
      ...
      I want to replace the missing values with the existing ones. So every unique date should have the same values. I hope this sheds more light on my question.

      Comment


      • #4
        See mipolate from SSC https://www.statalist.org/forums/for...-interpolation and especially its groupwise option.

        Comment


        • #5
          Thanks Nick! That helps me!

          Comment


          • #6

            Another simple method:

            Code:
            egen max = max(pfirt), by(datadate)
            egen min = min(pfirt), by(datadate)
            assert max == min
            replace pfirt = max if missing(pfirt)
            Last edited by Nick Cox; 19 Jun 2018, 03:51.

            Comment

            Working...
            X