STATA-FORUM.DE

[AlexST]

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: Alles auswählen

* 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

[Staxa]

Sorry, I dont really get your problem and your example data is confusing. Can you just post a example from your real data so the data structure is more clear?

[AlexST]

Thank you for your answer. I try to break it down more simple. I sort my data by date. It would look like this:

Code: Alles auswählen

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.

[Staxa]

Assuming that there is no variation of pf1rt within one date you can use this code:

Code: Alles auswählen

bysort datadate: egen temp = max(pf1rt)
replace pf1rt = temp if missing(pf1rt)
drop temp


[AlexST]

Thank you! That's exactly what I need.