Filling missing values with existing ones if they have the s

Fragen zu Stata Syntax und Do-Files.

Filling missing values with existing ones if they have the s

Beitragvon AlexST » Fr 15. Jun 2018, 13:10

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
AlexST
 
Beiträge: 8
Registriert: Fr 15. Jun 2018, 13:07
Danke gegeben: 0
Danke bekommen: 0 mal in 0 Post

Re: Filling missing values with existing ones if they have t

Beitragvon Staxa » Fr 15. Jun 2018, 19:11

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?
Stata für Anfänger: www.statabook.com
Staxa
 
Beiträge: 281
Registriert: Di 27. Feb 2018, 12:56
Danke gegeben: 0
Danke bekommen: 0 mal in 0 Post

Re: Filling missing values with existing ones if they have t

Beitragvon AlexST » Sa 16. Jun 2018, 11:35

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.
AlexST
 
Beiträge: 8
Registriert: Fr 15. Jun 2018, 13:07
Danke gegeben: 0
Danke bekommen: 0 mal in 0 Post

Re: Filling missing values with existing ones if they have t

Beitragvon Staxa » Sa 16. Jun 2018, 18:55

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
Stata für Anfänger: www.statabook.com
Staxa
 
Beiträge: 281
Registriert: Di 27. Feb 2018, 12:56
Danke gegeben: 0
Danke bekommen: 0 mal in 0 Post

Re: Filling missing values with existing ones if they have t

Beitragvon AlexST » Di 19. Jun 2018, 10:43

Thank you! That's exactly what I need.
AlexST
 
Beiträge: 8
Registriert: Fr 15. Jun 2018, 13:07
Danke gegeben: 0
Danke bekommen: 0 mal in 0 Post


Zurück zu Stata Syntax

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 1 Gast

cron