Hello,
I have a question and I would be grateful if you could help me with my problem!
How can I carryforward the values of a variable for a maximum of 12 months with the last observation of the variable for every ID in my dataset?
For example I have a dataset where I have monthly data from 2010 to 2021, but I also want data in 2022. As it is a variable that does not change often, I want to use the data from 2021 for the year 2022 to expand the data set.
I also wanted to fill some gaps as I sometimes only have data in month 1 of year x, but want to have the data for every month of the year.
I then used th following code for his (which I found in this forum):
tsset id month
tsfill
bysort id: carryforward var, gen(var)
Now one of the problems is, that it uses the previous data until the year where there is again an observation. Ideally I want to only use this method in a range of the following 12 months after the first missing data and maximum until I have data again (so only replacing missings).
The second problem is that it only fills the data with the variable "var" but not the observations of other variables. So for example there is the variable "region", but this variable will not be continued.
I then would use the following code:
by id: replace region = region[1] if region ==""
But now I would have to do this for every variable in the dataset that stays constant over the time period. I would maybe use a slope as it is faster. Is there a better way?
The third problem is, that it only continues with the data until the last month where I already have the data, but as I said I also want to have it for the following year (for example 2022, if I do not have data for this year, but for 2021; or for 2018, if I have this data for 2017).
Thanks a alot in advance. I am grateful for your help!
Best,
Anela
I have a question and I would be grateful if you could help me with my problem!
How can I carryforward the values of a variable for a maximum of 12 months with the last observation of the variable for every ID in my dataset?
For example I have a dataset where I have monthly data from 2010 to 2021, but I also want data in 2022. As it is a variable that does not change often, I want to use the data from 2021 for the year 2022 to expand the data set.
I also wanted to fill some gaps as I sometimes only have data in month 1 of year x, but want to have the data for every month of the year.
I then used th following code for his (which I found in this forum):
tsset id month
tsfill
bysort id: carryforward var, gen(var)
Now one of the problems is, that it uses the previous data until the year where there is again an observation. Ideally I want to only use this method in a range of the following 12 months after the first missing data and maximum until I have data again (so only replacing missings).
The second problem is that it only fills the data with the variable "var" but not the observations of other variables. So for example there is the variable "region", but this variable will not be continued.
I then would use the following code:
by id: replace region = region[1] if region ==""
But now I would have to do this for every variable in the dataset that stays constant over the time period. I would maybe use a slope as it is faster. Is there a better way?
The third problem is, that it only continues with the data until the last month where I already have the data, but as I said I also want to have it for the following year (for example 2022, if I do not have data for this year, but for 2021; or for 2018, if I have this data for 2017).
Thanks a alot in advance. I am grateful for your help!
Best,
Anela
Comment